﻿'========================================================================
'功能:資產資料庫存取物件
'作者:andy
'日期: 2011/3/25
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Net.Mail
Namespace AssetManage.DAO

    Public Class AssetDAO
        Inherits OAMS.DAO.BaseDAO

        Dim _dbName As String

        ''' <summary>
        ''' 取得員工所屬公司的行事曆的工作日日曆PS:目前先以日曆天計算
        ''' </summary>
        ''' <param name="dd">某一日</param>1
        ''' <param name="days">N天(負數代表是往前推)</param>
        ''' <param name="UID">員工</param>
        ''' <returns>取得員工所屬公司的行事曆的工作日日曆PS:目前先以日曆天計算</returns>
        ''' <remarks>取得員工所屬公司的行事曆的工作日日曆PS:目前先以日曆天計算</remarks>
        Public Function GetWorkDate(ByVal dd As DateTime, ByVal days As Integer, ByVal UID As String) As DateTime
            Return dd.AddDays(days)
        End Function
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub
        ''' <summary>
        ''' 找出尚在簽核中的資料
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetApprovingData() As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine(" select asset_serialNo ,ApplyFormNo,Apply from  V_AST_ASSETALL where isnull(Apply,'')<>'' ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        End Function

        ''' <summary>
        ''' 目前已不在簽核中的資料需要加以清除
        ''' </summary>
        ''' <param name="asset_serialNo"></param>
        ''' <remarks></remarks>
        Public Sub ClearApprovingData(ByVal asset_serialNo As String)
            Dim sb As New StringBuilder()
            sb.AppendLine(" update  V_AST_ASSETALL set AppID=isnull(AppID,0)+1,Apply=''  where asset_serialNo='" + asset_serialNo + "'")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString())
        End Sub

        Public Sub InsertReSendMailLog(ByVal AssetNo As String, ByVal UID As String)
            'ModiType -->   0:.新增1.異動2.移轉3.回報4.繳回5.取消回報6.寄信
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}


            sb.AppendLine(" update V_AST_ASSETALL set ReturnDeadLineNow =DATEADD (day,Resenddays,@Date1),ResendDate =@Date1,ResendTimesNow =ResendTimesNow+1,AppID=isnull(AppID,0)+1 ")
            sb.AppendLine(" where asset_serialNo=@AssetNo  ")
            'sb.AppendLine("declare @SeqNo int select @SeqNo=max(SeqNo) from R_AST_ASSETLog  where asset_SerialNo=@AssetNo    set @SeqNo=isnull(@SeqNo,0)+1  ")



            sqlParams(0) = New SqlParameter("@AssetNo", SqlDbType.NVarChar, AssetNo.Length)
            sqlParams(0).Value = AssetNo

            sqlParams(1) = New SqlParameter("@Date1", SqlDbType.DateTime)
            sqlParams(1).Value = Today

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            Dim owner As String = ""
            Dim dept As String = ""
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Dim assetdef_id As String = ""
            Dim HouseID As String = ""
            Try
                Dim dt As DataTable = GetAssetData("", "", AssetNo, "", "", "", "", "", "")
                owner = dt.Rows(0)("asset_owner").ToString
                dept = uDao.GetUsers(owner).Rows(0)("Dept_ID").ToString
                assetdef_id = dt.Rows(0)("assetdef_id").ToString
                HouseID = dt.Rows(0)("HouseID").ToString
            Catch ex As Exception

            End Try

            InsertMailLog("6", AssetNo, UID, owner, dept, assetdef_id, HouseID)
        End Sub
        Public Function GetAssetResendData() As DataSet
            Dim sb As New StringBuilder()

            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            sqlParams(0) = New SqlParameter("@Day1", SqlDbType.DateTime)
            sqlParams(0).Value = Today


            '===========================

            sb.AppendLine("update V_AST_ASSETALL set ReturnDeadLineNow=ReturnDeadLine ,AppID=isnull(AppID,0)+1  where ReturnDeadLineNow is null ")

            sb.AppendLine(" select A.assetdef_id as asset_type,B.[assetdef_nameEN]  as name,A.asset_owner as  keeping_person,emp_name as UserName,C.E_Mail as EMail,count(A.assetdef_id) as Qty from V_AST_ASSETALL A ")
            sb.AppendLine(" left join C_AST_ASSETDEF  B on A.assetdef_id=B.def_id left join AUOREF.dbo.emp_data_all   C On A.asset_owner=C.emp_no    ")
            sb.AppendLine("where ReturnBy='' and @Day1>=  dateadd(day,resenddays,ReturnDeadLineNow) and ResendTimesNow  <ResendTimes")
            sb.AppendLine("group by A.assetdef_id, A.asset_owner,B.[assetdef_nameEN],C.emp_name,C.E_Mail order by A.asset_owner,A.assetdef_id ")

            sb.AppendLine(" select    A.asset_owner as  keeping_person ,count(A.assetdef_id) as Qty  from V_AST_ASSETALL A ")
            sb.AppendLine(" left join C_AST_ASSETDEF  B on A.assetdef_id=B.def_id left join AUOREF.dbo.emp_data_all  C On A.asset_owner=C.emp_no   ")

            sb.AppendLine("where  ReturnBy='' and @Day1>=  dateadd(day,resenddays,ReturnDeadLineNow) and ResendTimesNow  <ResendTimes")
            sb.AppendLine("group by  A.asset_owner  ")


            sb.AppendLine(" select    A.asset_serialNo  from V_AST_ASSETALL A ")
            sb.AppendLine(" left join C_AST_ASSETDEF  B on A.assetdef_id=B.def_id left join AUOREF.dbo.emp_data_all  C On A.asset_owner=C.emp_no    ")

            sb.AppendLine("where  ReturnBy='' and @Day1>=  dateadd(day,resenddays,ReturnDeadLineNow) and ResendTimesNow  <ResendTimes")




            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Function
        Public Function GetOAUsingType(ByVal kind As String) As DataTable
            'Dim sb As New StringBuilder()
            'If kind = "1" Then
            '    sb.Append("select null as id, '' as name union  ")
            'End If
            'sb.AppendLine(" select id as id ,name as name  from OA_UsingType order by id ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)



            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)

            Try
                con.Open()

                Dim sb As New StringBuilder()
                If kind = "1" Then
                    sb.AppendLine("select '' as id, '' as name union")
                End If
                sb.AppendLine("Select A.id,A.name from OA_UsingType A")
               

                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()
                Return oTable

            Catch ex As Exception
                con.Close()
                Throw ex
            Finally


            End Try
        End Function
        Public Function GetOAStoreHouse(ByVal kind As String) As DataTable


            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)

            Try
                con.Open()

                Dim sb As New StringBuilder()

                sb.AppendLine("Select A.id,A.name,A.desc   from oa_storehouse A")
                If kind = "1" Then
                    sb.AppendLine("union select '' as id,'' as  name,''  as desc1   order by id  ")
                End If

                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()

                Return oTable

            Catch ex As Exception
                con.Close()
                Throw ex
            Finally


            End Try
        End Function


        ''' <summary>
        ''' 取得資產類別資料
        ''' </summary>
        ''' <param name="kind">0=正常,1=加一筆空白</param>
        ''' <returns>取得資產類別資料</returns>
        ''' <remarks>取得資產類別資料</remarks>
        Public Function GetAssetType(ByVal kind As String) As DataTable
            Dim sb As New StringBuilder()
            If kind = "1" Then
                sb.Append("select null as id, '' as name union  ")
            End If
            sb.AppendLine(" select def_id as id ,rtrim(assetdef_nameCN) as name  from C_AST_ASSETDEF ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        End Function
        Public Function GetOAStatus(ByVal kind As String) As DataTable
            'Dim sb As New StringBuilder()
            'If kind = "1" Then
            '    sb.Append("select null as id, '' as name union  ")
            'End If
            'sb.AppendLine(" select id as id ,name as name  from OA_Status order by id ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)

            Try
                con.Open()

                Dim sb As New StringBuilder()
                If kind = "1" Then
                    sb.AppendLine("select '' as id, '' as name union")
                End If
                sb.AppendLine("Select A.id,A.name from oa_status A")


                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()

                Return oTable

            Catch ex As Exception
                con.Close()
                Throw ex
            Finally
              
            End Try


        End Function


        Public Function GetOAFactory(ByVal kind As String) As DataTable
            'Dim sb As New StringBuilder()
            'If kind = "1" Then
            '    sb.Append("select '' as id, '' as name union  ")
            'End If
            'sb.AppendLine(" select Factory_ID as id ,Factory_Name as name  from OA_Factory order by id ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)


            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)

            Try
                con.Open()

                Dim sb As New StringBuilder()
                If kind = "1" Then
                    sb.AppendLine("select '' as id, '' as name union")
                End If
                sb.AppendLine("Select distinct site_name as id ,site_name as name from `auoref`.`emp_data_all`  where ifnull(site_name,'')<>''  and active='Y'")


                cmd = New MySqlCommand(sb.ToString(), con)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()

                Return oTable

            Catch ex As Exception
                con.Close()
                Throw ex
            Finally


            End Try

        End Function

        ''' <summary>
        ''' 取得某一員工所有資產或某一類別資產
        ''' </summary>
        ''' <param name="AssetType">資產類別空白代表全部</param>
        ''' <param name="UserID">員工編號</param>
        ''' <returns>取得某一員工所有資產或某一類別資產</returns>
        ''' <remarks>取得某一員工所有資產或某一類別資產</remarks>
        Public Function GetAssetDataByAssettypeUID(ByVal AssetType As String, ByVal UserID As String) As DataSet
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}

            sb.AppendLine(" select   *  from V_AST_ASSETALL  ")
            sb.AppendLine("where 1=1  and asset_status='Distributed'")
            sb.AppendLine(" and assetdef_id like  @asset_type+'%'")
            sb.AppendLine(" and  asset_owner=@UserID")


            sqlParams(0) = New SqlParameter("@asset_type", SqlDbType.NVarChar, AssetType.Length)
            sqlParams(0).Value = AssetType
            sqlParams(1) = New SqlParameter("@UserID", SqlDbType.NVarChar, UserID.Length)
            sqlParams(1).Value = UserID
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Function



        ''' <summary>
        ''' 新增一筆資料到Log中,並且將已回報的資料清除
        ''' </summary>
        ''' <param name="AssetNo">資產編號</param>
        ''' <param name="UID">員工編號</param>
        ''' <param name="ReturnDeadLine">回報截止日</param>
        ''' <param name="ResendDays">重送天數</param>
        ''' <param name="ResendTimes">重送次數</param>
        '''<param name="MailSubject">主旨</param>
        ''' <param name="MailBody">內容</param>
        ''' <remarks></remarks>
        Public Sub InsertSendMailLog(ByVal AssetNo As String, ByVal UID As String, ByVal ReturnDeadLine As DateTime, ByVal ResendDays As Integer, ByVal ResendTimes As Integer, ByVal MailSubject As String, ByVal MailBody As String, ByVal ParamID As Integer)
            'ModiType -->   0:.新增1.異動2.移轉3.回報4.繳回5.取消回報6.寄信
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(8) {}


            sb.AppendLine(" update  V_AST_ASSETALL set   ReturnDate=null,AppID=isnull(AppID,0)+1 ,ReturnBy='',ReturnDeadLine=@ReturnDeadLine,ResendDays=@ResendDays ,ResendTimes=@ResendTimes ,ResendTimesNow=0,ResendDate=dateadd(day,@ResendDays,@ReturnDeadLine),MailSubject=@MailSubject ,MailBody=@MailBody ,SendDate=@SendDate,ParamID= @ParamID  ")
            sb.AppendLine(" where asset_serialNo=@AssetNo  ")
            'sb.AppendLine("declare @SeqNo int select @SeqNo=max(SeqNo) from R_AST_ASSETLog  where asset_SerialNo=@AssetNo    set @SeqNo=isnull(@SeqNo,0)+1  ")
            'sb.AppendLine("insert    R_AST_ASSETLog ( ModiType, SeqNo, ReturnDate, ReturnBy, ReturnDeadLine, ResendDays, ResendTimes, ResendTimesNow, ResendDate, MailSubject, MailBody, assetdef_id, asset_serialNo, asset_owner, asset_status, asset_cdate, Class, AssetNumber, Ownerid, EXt, User2, Dept, Location, ComputerName, Model, CPU, FDD, CD_R,  CD_RW, MAC1, MAC2, PS, NBLabel, Mail, Remark, OS, HDD, RAM, UsingType, MonitorSize, Factory, TEST11, test456, UserName, User1) ")
            'sb.AppendLine(" select '6',@SeqNo,ReturnDate,ReturnBy,ReturnDeadLine,ResendDays ,ResendTimes ,0,null ,@MailSubject,@MailBody,assetdef_id, asset_serialNo, asset_owner, asset_status, asset_cdate, Class, AssetNumber, Ownerid, EXt, User2, Dept, Location, ComputerName, Model, CPU, FDD, CD_R,  CD_RW, MAC1, MAC2, PS, NBLabel, Mail, Remark, OS, HDD, RAM, UsingType, MonitorSize, Factory, TEST11, test456, UserName, User1")
            'sb.AppendLine(" from V_AST_ASSETALL where asset_SerialNo=@AssetNo ")
            '===========================.

            sqlParams(0) = New SqlParameter("@AssetNo", SqlDbType.NVarChar, AssetNo.Length)
            sqlParams(0).Value = AssetNo
            sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar, UID.Length)
            sqlParams(1).Value = UID
            sqlParams(2) = New SqlParameter("@ReturnDeadLine", SqlDbType.DateTime)
            sqlParams(2).Value = ReturnDeadLine
            sqlParams(3) = New SqlParameter("@ResendDays", SqlDbType.Int)
            sqlParams(3).Value = ResendDays
            sqlParams(4) = New SqlParameter("@ResendTimes", SqlDbType.Int)
            sqlParams(4).Value = ResendTimes
            sqlParams(5) = New SqlParameter("@MailSubject", SqlDbType.NVarChar, MailSubject.Length)
            sqlParams(5).Value = MailSubject
            sqlParams(6) = New SqlParameter("@MailBody", SqlDbType.NVarChar, MailBody.Length)
            sqlParams(6).Value = MailBody
            sqlParams(7) = New SqlParameter("@SendDate", SqlDbType.DateTime)
            sqlParams(7).Value = Now
            sqlParams(8) = New SqlParameter("@ParamID", SqlDbType.Int)
            sqlParams(8).Value = ParamID
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            Dim owner As String = ""
            Dim dept As String = ""
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Dim assetdef_id As String = ""
            Dim HouseID As String = ""
            Try
                Dim dt As DataTable = GetAssetData("", "", AssetNo, "", "", "", "", "", "")
                owner = dt.Rows(0)("asset_owner").ToString
                dept = uDao.GetUsers(owner).Rows(0)("Dept_ID").ToString
                assetdef_id = dt.Rows(0)("assetdef_id").ToString
                HouseID = dt.Rows(0)("HouseID").ToString
            Catch ex As Exception

            End Try
            InsertMailLog("6", AssetNo, UID, owner, dept, assetdef_id, HouseID)
        End Sub

        Public Sub InsertSendMailLogWS(ByVal AssetNo As String, ByVal UID As String, ByVal ReturnDeadLine As DateTime, ByVal ResendDays As Integer, ByVal ResendTimes As Integer, ByVal MailSubject As String, ByVal MailBody As String, ByVal ParamID As Integer)
            'ModiType -->   0:.新增1.異動2.移轉3.回報4.繳回5.取消回報6.寄信
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(9) {}


            sb.AppendLine(" update  V_AST_ASSETALL set   ReturnDate=null,AppID=isnull(AppID,0)+1 ,ReturnBy='',ReturnDeadLine=@ReturnDeadLine,ResendDays=@ResendDays ,ResendTimes=@ResendTimes ,ResendTimesNow=0,ResendDate=dateadd(day,@ResendDays,@ReturnDeadLine),MailSubject=@MailSubject ,MailBody=@MailBody ,SendDate=@SendDate,ParamID= @ParamID  ")
            sb.AppendLine(" where asset_serialNo=@AssetNo  ")
            'sb.AppendLine("declare @SeqNo int select @SeqNo=max(SeqNo) from R_AST_ASSETLog  where asset_SerialNo=@AssetNo    set @SeqNo=isnull(@SeqNo,0)+1  ")
            'sb.AppendLine("insert    R_AST_ASSETLog ( ModiType, SeqNo, ReturnDate, ReturnBy, ReturnDeadLine, ResendDays, ResendTimes, ResendTimesNow, ResendDate, MailSubject, MailBody, assetdef_id, asset_serialNo, asset_owner, asset_status, asset_cdate, Class, AssetNumber, Ownerid, EXt, User2, Dept, Location, ComputerName, Model, CPU, FDD, CD_R,  CD_RW, MAC1, MAC2, PS, NBLabel, Mail, Remark, OS, HDD, RAM, UsingType, MonitorSize, Factory, TEST11, test456, UserName, User1) ")
            'sb.AppendLine(" select '6',@SeqNo,ReturnDate,ReturnBy,ReturnDeadLine,ResendDays ,ResendTimes ,0,null ,@MailSubject,@MailBody,assetdef_id, asset_serialNo, asset_owner, asset_status, asset_cdate, Class, AssetNumber, Ownerid, EXt, User2, Dept, Location, ComputerName, Model, CPU, FDD, CD_R,  CD_RW, MAC1, MAC2, PS, NBLabel, Mail, Remark, OS, HDD, RAM, UsingType, MonitorSize, Factory, TEST11, test456, UserName, User1")
            'sb.AppendLine(" from V_AST_ASSETALL where asset_SerialNo=@AssetNo ")
            '===========================.

            sqlParams(0) = New SqlParameter("@AssetNo", SqlDbType.NVarChar, AssetNo.Length)
            sqlParams(0).Value = AssetNo
            sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar, UID.Length)
            sqlParams(1).Value = UID
            sqlParams(2) = New SqlParameter("@ReturnDeadLine", SqlDbType.DateTime)
            sqlParams(2).Value = ReturnDeadLine
            sqlParams(3) = New SqlParameter("@ResendDays", SqlDbType.Int)
            sqlParams(3).Value = ResendDays
            sqlParams(4) = New SqlParameter("@ResendTimes", SqlDbType.Int)
            sqlParams(4).Value = ResendTimes
            sqlParams(5) = New SqlParameter("@MailSubject", SqlDbType.NVarChar, MailSubject.Length)
            sqlParams(5).Value = MailSubject
            sqlParams(6) = New SqlParameter("@MailBody", SqlDbType.NVarChar, MailBody.Length)
            sqlParams(6).Value = MailBody
            sqlParams(7) = New SqlParameter("@SendDate", SqlDbType.DateTime)
            sqlParams(7).Value = Now
            sqlParams(8) = New SqlParameter("@ParamID", SqlDbType.Int)
            sqlParams(8).Value = ParamID
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            Dim owner As String = ""
            Dim dept As String = ""
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Dim assetdef_id As String = ""
            Dim HouseID As String = ""
            Try
                Dim dt As DataTable = GetAssetData("", "", AssetNo, "", "", "", "", "", "")
                owner = dt.Rows(0)("asset_owner").ToString
                dept = uDao.GetUsers(owner).Rows(0)("Dept_ID").ToString
                assetdef_id = dt.Rows(0)("assetdef_id").ToString
                HouseID = dt.Rows(0)("HouseID").ToString
            Catch ex As Exception

            End Try
            InsertMailLog("6", AssetNo, UID, owner, dept, assetdef_id, HouseID)
        End Sub



        Public Sub InsertMailLog(ByVal ModiType As String, ByVal Asset_serialNo As String, ByVal UID As String, ByVal owner As String, ByVal dept As String, ByVal assetdef_id As String, ByVal HouseID As String)

            Dim sb1 As StringBuilder = New StringBuilder()
            sb1.AppendLine(" select max(SeqNo)+1 as MAX1 from v_ast_assetalllog where asset_serialNo=?asset_serialNo ")

            Dim oTable As Data.DataTable
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection
            con = myMYSqlConn(_dbName)
            cmd = New MySqlCommand(sb1.ToString(), con)
            cmd.Parameters.AddWithValue("?asset_serialNo", Asset_serialNo)
            Dim oDbAdapter As MySqlDataAdapter
            oDbAdapter = New MySqlDataAdapter(cmd)
            oTable = New Data.DataTable()
            oDbAdapter.Fill(oTable)
            Dim max As Integer
            If oTable.Rows(0)("MAX1").ToString <> "" Then
                max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
            Else
                max = 1
            End If
            Try
                con.Open()
                sb1.Length = 0
                sb1.AppendLine("insert into v_ast_assetalllog (ModiType,SeqNo,asset_serialNo,ModiUID,ModiDate,asset_owner,Dept,assetdef_id,HouseID) values (?ModiType,?SeqNo,?asset_serialNo,?ModiUID,?ModiDate,?asset_owner,?Dept,?assetdef_id,?HouseID)")
                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?ModiType", ModiType)
                cmd.Parameters.AddWithValue("?SeqNo", max)
                cmd.Parameters.AddWithValue("?asset_serialNo", Asset_serialNo)
                cmd.Parameters.AddWithValue("?ModiUID", UID)
                cmd.Parameters.AddWithValue("?ModiDate", Now)
                cmd.Parameters.AddWithValue("?asset_owner", owner)
                cmd.Parameters.AddWithValue("?Dept", dept)
                cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                cmd.Parameters.AddWithValue("?HouseID", HouseID)
                cmd.ExecuteNonQuery()


            Catch ex As Exception
            Finally

                con.Close()
            End Try


        End Sub



        Public Function GetAssetGroupData(ByVal AssetType As String, ByVal DeptNo As String, ByVal TakeOfficeDate As String, ByVal HouseID As String, ByVal TopN As String, ByVal EquQty As String, ByVal UserID As String, ByVal AssetNo As String) As DataSet
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(7) {}


            If TopN = "0" Or TopN = "" Then
                TopN = "9999999"
            End If

            If EquQty = "0" Or EquQty = "" Then
                EquQty = "0"
            End If


            sb.AppendLine(" select   Top " + TopN + " A.asset_owner as  keeping_person ,count(A.assetdef_id) as Qty into #AA from V_AST_ASSETALL A ")
            sb.AppendLine(" left join C_AST_ASSETDEF  B on A.assetdef_id=B.def_id left join AUOREF.dbo.emp_data_all  C On A.asset_owner=C.emp_no ")
            sb.AppendLine("where 1=1  and A.asset_status='Distributed'")
            sb.AppendLine(" and (A.assetdef_id = @asset_type or @asset_type='' )")
            sb.AppendLine(" and C.org_id like @DeptNo+'%'")
            sb.AppendLine(" and C.come_date<=@TakeOfficeDate")
            sb.AppendLine(" and (@HouseID='0' or isnull(A.HouseID,'1')=@HouseID) ")
            sb.AppendLine(" and (@UserID='' or A.asset_owner=@UserID)")
            sb.AppendLine(" and (@AssetNo='' or A.asset_SerialNo=@AssetNo)")
            sb.AppendLine(" and (C.active='Y' and (C.quit_date is null or C.quit_date>getdate()) )")

            sb.AppendLine("group by  A.asset_owner  ")
            sb.AppendLine(" having  count(A.assetdef_id) >= @EquQty   order by count(A.assetdef_id) desc ")

            '===========================
            sb.AppendLine(" select A.assetdef_id as asset_type,B.[assetdef_nameEN]  as name,A.asset_owner as  keeping_person,C.emp_name as UserName,C.e_mail as EMail,count(A.assetdef_id) as Qty from V_AST_ASSETALL A ")
            sb.AppendLine(" left join C_AST_ASSETDEF  B on A.assetdef_id=B.def_id   left join  AUOREF.dbo.emp_data_all C On A.asset_owner=C.emp_no   ")
            sb.AppendLine("where 1=1  and A.asset_status='Distributed'")
            sb.AppendLine(" and (A.assetdef_id = @asset_type or @asset_type='' )")
            sb.AppendLine(" and C.org_id like @DeptNo+'%'")
            sb.AppendLine(" and C.come_date<=@TakeOfficeDate")
            sb.AppendLine(" and (@HouseID='0' or isnull(A.HouseID,'1')=@HouseID) ")
            sb.AppendLine(" and (C.active='Y' and (C.quit_date is null or C.quit_date>getdate()) )")

            sqlParams(0) = New SqlParameter("@asset_type", SqlDbType.NVarChar, AssetType.Length)
            sqlParams(0).Value = AssetType
            sqlParams(1) = New SqlParameter("@DeptNo", SqlDbType.NVarChar, DeptNo.Length)
            sqlParams(1).Value = DeptNo
            If TakeOfficeDate <> "" Then
                sqlParams(2) = New SqlParameter("@TakeOfficeDate", SqlDbType.DateTime)
                sqlParams(2).Value = TakeOfficeDate
            Else
                sqlParams(2) = New SqlParameter("@TakeOfficeDate", SqlDbType.DateTime)
                sqlParams(2).Value = DateTime.Today
            End If
            sqlParams(3) = New SqlParameter("@HouseID", SqlDbType.NVarChar, HouseID.Length)
            sqlParams(3).Value = HouseID

            sqlParams(4) = New SqlParameter("@EquQty", SqlDbType.Int)
            sqlParams(4).Value = EquQty
            sqlParams(5) = New SqlParameter("@UserID", SqlDbType.NVarChar, UserID.Length)
            sqlParams(5).Value = UserID
            sqlParams(6) = New SqlParameter("@AssetNo", SqlDbType.NVarChar, AssetNo.Length)
            sqlParams(6).Value = AssetNo

            sb.AppendLine(" and A.asset_owner in (select keeping_person from #AA )")

            sb.AppendLine("group by A.assetdef_id, A.asset_owner,B.[assetdef_nameEN],C.emp_name,C.e_mail order by A.asset_owner,A.assetdef_id ")
            sb.AppendLine("Select * from #AA")
            sb.AppendLine(" drop table #AA")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Function

        Public Function GetAssetData(ByVal assetdef_id As String, ByVal asset_owner As String, ByVal asset_SerialNo As String, ByVal Dept As String, ByVal HouseID As String, ByVal MAC As String, ByVal ComputerName As String, ByVal IP As String, ByVal asset_status As String) As DataTable
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(7) {}
            If assetdef_id = "" Then
                assetdef_id = "0"
            End If
            If HouseID = "" Then
                HouseID = "0"
            End If

            sb.AppendLine(" select F.assetdef_nameEN as DefName ,E.emp_name as CrtName  ,B.emp_name as ownerName,A.User1 as UseName,D.emp_name as ModiName, A.*  from V_AST_ASSETALL A  left join AUOREF.dbo.emp_data_all  B on A.asset_owner=B.emp_no   left join AUOREF.dbo.emp_data_all C on A.User2=C.emp_no  left join AUOREF.dbo.emp_data_all  D on A.ModiUID=D.emp_no    left join AUOREF.dbo.emp_data_all E on A.CrtUID=E.emp_no   left join  C_AST_ASSETDEF F on A.assetdef_id=F.def_id ")
            sb.AppendLine("where 1=1 ")
            sb.AppendLine(" and (   assetdef_id=@assetdef_id OR @assetdef_id=0) ")
            sb.AppendLine(" and  (isnull(asset_owner,'') like '%'+@asset_owner+'%')")
            sb.AppendLine(" and  (asset_serialNo like '%'+@asset_serialNo+'%' )")
            sb.AppendLine(" and  (isNull(B.org_id,'') like '%'+@Dept+'%')")
            sb.AppendLine(" and  (isNull(MAC1,'') like '%'+@MAC+'%' or isNull(MAC2,'') like '%'+@MAC+'%' )")
            sb.AppendLine(" and  (isNull(ComputerName,'') like '%'+@ComputerName+'%')")
            sb.AppendLine(" and (asset_status=@asset_status or @asset_status='')")
            sb.AppendLine(" and (HouseID=@HouseID or @HouseID='0')")

            sb.AppendLine(" order by  F.assetdef_nameEN ,A.asset_serialNo ")


            '  sb.AppendLine(" and  (IP=@IP or IP=@IP or @IP='')")

            sqlParams(0) = New SqlParameter("@assetdef_id", SqlDbType.Int)
            sqlParams(0).Value = assetdef_id
            sqlParams(1) = New SqlParameter("@asset_owner", SqlDbType.NVarChar, asset_owner.Length)
            sqlParams(1).Value = asset_owner
            sqlParams(2) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, asset_SerialNo.Length)
            sqlParams(2).Value = asset_SerialNo
            sqlParams(3) = New SqlParameter("@Dept", SqlDbType.NVarChar, Dept.Length)
            sqlParams(3).Value = Dept
            sqlParams(4) = New SqlParameter("@MAC", SqlDbType.NVarChar, MAC.Length)
            sqlParams(4).Value = MAC
            sqlParams(5) = New SqlParameter("@ComputerName", SqlDbType.NVarChar, ComputerName.Length)
            sqlParams(5).Value = ComputerName
            'sqlParams(6) = New SqlParameter("@IP", SqlDbType.NVarChar, IP.Length)
            'sqlParams(6).Value = IP
            sqlParams(6) = New SqlParameter("@asset_status", SqlDbType.NVarChar)
            sqlParams(6).Value = asset_status
            sqlParams(7) = New SqlParameter("@HouseID", SqlDbType.NVarChar)
            sqlParams(7).Value = HouseID
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function


        Public Function GetAssetData1(ByVal assetdef_id As String, ByVal asset_owner As String, ByVal asset_SerialNo As String, ByVal Dept As String, ByVal HouseID As String, ByVal MAC As String, ByVal ComputerName As String, ByVal IP As String, ByVal asset_status As String) As DataTable
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(7) {}
            If assetdef_id = "" Then
                assetdef_id = "0"
            End If
            If HouseID = "" Then
                HouseID = "0"
            End If
            sb.AppendLine(" select F.assetdef_nameEN as DefName ,E.emp_name as CrtName  ,B.emp_name as ownerName,A.User1 as UseName,D.emp_name as ModiName, A.*  from V_AST_ASSETALL A  left join AUOREF.dbo.emp_data_all  B on A.asset_owner=B.emp_no  left join AUOREF.dbo.emp_data_all C on A.User2=C.emp_no  left join AUOREF.dbo.emp_data_all  D on A.ModiUID=D.emp_no    left join AUOREF.dbo.emp_data_all E on A.CrtUID=E.emp_no  left join  C_AST_ASSETDEF F on A.assetdef_id=F.def_id ")
            sb.AppendLine("where 1=1 ")
            sb.AppendLine(" and (   assetdef_id=@assetdef_id OR @assetdef_id=0) ")
            sb.AppendLine(" and  (isNull(asset_owner,'') like '%'+@asset_owner+'%')")
            sb.AppendLine(" and  (asset_serialNo like '%'+@asset_serialNo+'%' )")
            sb.AppendLine(" and  (isNull(B.org_id,'') like '%'+@Dept+'%')")
            sb.AppendLine(" and  (isNull(MAC1,'') like '%'+@MAC+'%' or isNull(MAC2,'') like '%'+@MAC+'%' )")
            sb.AppendLine(" and  (ComputerName like '%'+@ComputerName+'%')")
            sb.AppendLine(" and (asset_status=@asset_status or @asset_status='')")
            sb.AppendLine(" and (HouseID=@HouseID or @HouseID='0')")
            sb.AppendLine(" and isNull(Apply,'')='' ")
            '  sb.AppendLine(" and  (IP=@IP or IP=@IP or @IP='')")
            sb.AppendLine(" order by  F.assetdef_nameEN ,A.asset_serialNo ")
            sqlParams(0) = New SqlParameter("@assetdef_id", SqlDbType.Int)
            sqlParams(0).Value = assetdef_id
            sqlParams(1) = New SqlParameter("@asset_owner", SqlDbType.NVarChar, asset_owner.Length)
            sqlParams(1).Value = asset_owner
            sqlParams(2) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, asset_SerialNo.Length)
            sqlParams(2).Value = asset_SerialNo
            sqlParams(3) = New SqlParameter("@Dept", SqlDbType.NVarChar, Dept.Length)
            sqlParams(3).Value = Dept
            sqlParams(4) = New SqlParameter("@MAC", SqlDbType.NVarChar, MAC.Length)
            sqlParams(4).Value = MAC
            sqlParams(5) = New SqlParameter("@ComputerName", SqlDbType.NVarChar, ComputerName.Length)
            sqlParams(5).Value = ComputerName
            'sqlParams(6) = New SqlParameter("@IP", SqlDbType.NVarChar, IP.Length)
            'sqlParams(6).Value = IP
            sqlParams(6) = New SqlParameter("@asset_status", SqlDbType.NVarChar)
            sqlParams(6).Value = asset_status
            sqlParams(7) = New SqlParameter("@HouseID", SqlDbType.NVarChar)
            sqlParams(7).Value = HouseID
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function





        Public Function GetAssetReturnData(ByVal assetdef_id As String, ByVal asset_owner As String, ByVal asset_SerialNo As String, ByVal Kind As String) As DataTable
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(3) {}
            If assetdef_id = "" Then
                assetdef_id = "0"
            End If
            sb.AppendLine(" select F.assetdef_nameEN as DefName ,E.emp_name as CrtName  ,B.emp_name as ownerName,A.User1 as UseName,D.emp_name as ModiName, A.*  from V_AST_ASSETALL A  left join AUOREF.dbo.emp_data_all B on A.asset_owner=B.emp_no   left join AUOREF.dbo.emp_data_all C on A.User2=C.emp_no   left join AUOREF.dbo.emp_data_all D on A.ModiUID=D.emp_no   left join AUOREF.dbo.emp_data_all E on A.CrtUID=E.emp_no  left   join  C_AST_ASSETDEF F on A.assetdef_id=F.def_id ")
            sb.AppendLine("where 1=1  and asset_status='Distributed'")
            sb.AppendLine(" and (   assetdef_id=@assetdef_id OR @assetdef_id=0) ")
            sb.AppendLine(" and  (asset_owner like '%'+@asset_owner+'%')")
            sb.AppendLine(" and  (asset_serialNo like '%'+@asset_serialNo+'%' )  and not ReturnDeadLine is null")

            If Kind = "1" Then
                sb.AppendLine(" and isNull(A.ReturnBy,'') ='' ") 'Kind=1 : 回報'Kind=2 :取消回報
            Else
                sb.AppendLine(" and isNull(A.ReturnBy,'') <>'' ")
            End If

            sqlParams(0) = New SqlParameter("@assetdef_id", SqlDbType.Int)
            sqlParams(0).Value = assetdef_id
            sqlParams(1) = New SqlParameter("@asset_owner", SqlDbType.NVarChar, asset_owner.Length)
            sqlParams(1).Value = asset_owner
            sqlParams(2) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, asset_SerialNo.Length)
            sqlParams(2).Value = asset_SerialNo

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function




        '''' <summary>
        '''' 新增OA資產回報紀錄
        '''' </summary>
        '''' <param name="AsseetNo">資產編號</param>
        '''' <param name="AssetType">資產型態</param>
        '''' <param name="Owner">資產保管人</param>
        '''' <param name="sendMan">寄信人/SYstem</param>
        '''' <param name="ReplyDeadLine">回報截止日</param>
        '''' <param name="ResendDays">重送天數</param>
        '''' <param name="ResendTimes">重送次數</param>
        '''' <remarks></remarks>
        'Public Sub InsertAssetReplyLog(ByVal AsseetNo As String, ByVal AssetType As String, ByVal Owner As String, ByVal sendMan As String, ByVal ReplyDeadLine As String, ByVal ResendDays As Integer, ByVal ResendTimes As Integer)
        '    Dim sb As New StringBuilder()
        '    Dim sqlParams As SqlParameter() = New SqlParameter(3) {}
        '    sqlParams(0) = New SqlParameter("@AsseetNo", SqlDbType.NVarChar, AsseetNo.Length)
        '    sqlParams(0).Value = AsseetNo
        '    sqlParams(1) = New SqlParameter("@AssetType", SqlDbType.NVarChar, AssetType.Length)
        '    sqlParams(1).Value = AssetType

        '    sqlParams(2) = New SqlParameter("@Owner", SqlDbType.NVarChar, Owner.Length)
        '    sqlParams(2).Value = Owner
        '    sb.AppendLine("Select * from R_AST_ASSET where 1=1  asset_no like @AssetNo+'%' and( keeping_person=@Owner or  @Owner=''  ) and asset_type like  @AssetType +'%'")
        '    Dim dt As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        '    For Each dr As DataRow In dt.Rows

        '    Next

        'End Sub
        Public Sub SaveAssetData(ByVal UsingType2 As String, ByVal StoreHouse As String, ByVal StoreLocation As String, ByVal User1 As String, ByVal UID As String, ByVal asset_serialNo As String, ByVal UsingDesc As String)
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(7) {}


            sb.AppendLine(" update  V_AST_ASSETALL set UsingType2=@UsingType2,AppID=isnull(AppID,0)+1,StoreHouse=@StoreHouse,StoreLocation=@StoreLocation,User1=@User1,UsingDesc=@UsingDesc,ModiDate=GetDate(),ModiUID=@UID where asset_serialNo=@asset_serialNo ")
            '===========================.



            sqlParams(0) = New SqlParameter("@UsingType2", SqlDbType.NVarChar, UsingType2.Length)
            sqlParams(0).Value = UsingType2

            sqlParams(1) = New SqlParameter("@StoreHouse", SqlDbType.NVarChar, StoreHouse.Length)
            sqlParams(1).Value = StoreHouse
            sqlParams(2) = New SqlParameter("@StoreLocation", SqlDbType.NVarChar, StoreLocation.Length)
            sqlParams(2).Value = StoreLocation
            sqlParams(3) = New SqlParameter("@User1", SqlDbType.NVarChar, User1.Length)
            sqlParams(3).Value = User1
            sqlParams(4) = New SqlParameter("@UID", SqlDbType.NVarChar, UID.Length)
            sqlParams(4).Value = UID
            sqlParams(5) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, asset_serialNo.Length)
            sqlParams(5).Value = asset_serialNo
            sqlParams(6) = New SqlParameter("@UsingDesc", SqlDbType.NVarChar, UsingDesc.Length)
            sqlParams(6).Value = UsingDesc

            InsertAssetLog(UsingType2, StoreHouse, StoreLocation, User1, UID, asset_serialNo, UsingDesc)
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

        End Sub


        Public Sub SaveAssetData(ByVal vo As AssetManage.VO.AssetVO, ByVal webPath As String)

            Dim fieldCount As Integer = 1
            Dim Fields = "asset_serialNo=@asset_serialNo"
            Dim Parms = ""

            If vo.assetdef_idChg Then
                fieldCount += 1
                ' Fields += ",assetdef_id"
                Parms += ",assetdef_id=@assetdef_id"
            End If

            If vo.asset_ownerChg Then
                fieldCount += 1
                'Fields += ",asset_owner"
                Parms += ",asset_owner=@asset_owner"
            End If
            If vo.asset_statusChg Then
                fieldCount += 1
                ' Fields += ",asset_status"
                Parms += ",asset_status=@asset_status"
            End If
            If vo.asset_cdateChg Then
                fieldCount += 1
                ' Fields += ",asset_cdate"
                Parms += ",asset_cdate=@asset_cdate"
            End If
            If vo.ClassChg Then
                fieldCount += 1
                'Fields += ",Class"
                Parms += ",Class=@Class"
            End If
            If vo.AssetNumberChg Then
                fieldCount += 1
                'Fields += ",AssetNumber"
                Parms += ",AssetNumber=@AssetNumber"
            End If
            If vo.OwneridChg Then
                fieldCount += 1
                ' Fields += ",Ownerid"
                Parms += ",Ownerid=@Ownerid"
            End If
            If vo.EXtChg Then
                fieldCount += 1
                ' Fields += ",EXt"
                Parms += ",EXt=@EXt"
            End If
            If vo.User1Chg Then
                fieldCount += 1
                ' Fields += ",User2"
                Parms += ",User1=@User1"
            End If

            If vo.DeptChg Then
                fieldCount += 1
                ' Fields += ",Dept"
                Parms += ",Dept=@Dept"
            End If
            If vo.LocationChg Then
                fieldCount += 1
                ' Fields += ",Location"
                Parms += ",Location=@Location"
            End If
            If vo.ComputerNameChg Then
                fieldCount += 1
                '  Fields += ",ComputerName"
                Parms += ",ComputerName=@ComputerName"
            End If
            If vo.ModelChg Then
                fieldCount += 1
                ' Fields += ",Model"
                Parms += ",Model=@Model"
            End If
            If vo.CPUChg Then
                fieldCount += 1
                ' Fields += ",CPU"
                Parms += ",CPU=@CPU"
            End If
            If vo.FDDChg Then
                fieldCount += 1
                ' Fields += ",FDD"
                Parms += ",FDD=@FDD"
            End If
            If vo.CD_RChg Then
                fieldCount += 1
                'Fields += ",CD_R"
                Parms += ",CD_R=@CD_R"
            End If
            If vo.CD_RWChg Then
                fieldCount += 1
                ' Fields += ",CD_RW"
                Parms += ",CD_RW=@CD_RW"
            End If

            If vo.MAC1Chg Then
                fieldCount += 1
                'Fields += ",MAC1"
                Parms += ",MAC1=@MAC1"
            End If
            If vo.MAC2Chg Then
                fieldCount += 1
                'Fields += ",MAC2"
                Parms += ",MAC2=@MAC2"
            End If
            If vo.PSChg Then
                fieldCount += 1
                'Fields += ",PS"
                Parms += ",PS=@PS"
            End If
            If vo.NBLabelChg Then
                fieldCount += 1
                '  Fields += ",NBLabel"
                Parms += ",NBLabel=@NBLabel"
            End If

            If vo.MailChg Then
                fieldCount += 1
                ' Fields += ",Mail"
                Parms += ",Mail=@Mail"
            End If
            If vo.RemarkChg Then
                fieldCount += 1
                ' Fields += ",Remark"
                Parms += ",Remark=@Remark"
            End If
            If vo.OSChg Then
                fieldCount += 1
                ' Fields += ",OS"
                Parms += ",OS=@OS"
            End If
            If vo.HDDChg Then
                fieldCount += 1
                ' Fields += ",HDD"
                Parms += ",HDD=@HDD"
            End If

            If vo.RAMChg Then
                fieldCount += 1
                ' Fields += ",RAM"
                Parms += ",RAM=@RAM"
            End If
            If vo.UsingTypeChg Then
                fieldCount += 1
                ' Fields += ",UsingType"
                Parms += ",UsingType=@UsingType"
            End If
            If vo.MonitorSizeChg Then
                fieldCount += 1
                '  Fields += ",MonitorSize"
                Parms += ",MonitorSize=@MonitorSize"
            End If
            If vo.FactoryChg Then
                fieldCount += 1
                ' Fields += ",Factory"
                Parms += ",Factory=@Factory"
            End If
            If vo.TEST11Chg Then
                fieldCount += 1
                ' Fields += ",TEST11"
                Parms += ",TEST11=@TEST11"
            End If

            If vo.test456Chg Then
                fieldCount += 1
                ' Fields += ",test456"
                Parms += ",test456=@test456"
            End If
            If vo.UserNameChg Then
                fieldCount += 1
                ' Fields += ",UserName"
                Parms += ",UserName=@UserName"
            End If
            If vo.User2Chg Then
                fieldCount += 1
                ' Fields += ",User1"
                Parms += ",User2=@User2"
            End If
            If vo.ReturnDateChg Then
                fieldCount += 1
                ' Fields += ",ReturnDate"
                Parms += ",ReturnDate=@ReturnDate"
            End If
            If vo.ReturnByChg Then
                fieldCount += 1
                ' Fields += ",ReturnBy"
                Parms += ",ReturnBy=@ReturnBy"
            End If
            If vo.ReturnDeadLineChg Then
                fieldCount += 1
                'Fields += ",ReturnDeadLine"
                Parms += ",ReturnDeadLine=@ReturnDeadLine"
            End If

            If vo.ResendDaysChg Then
                fieldCount += 1
                'Fields += ",ResendDays"
                Parms += ",ResendDays=@ResendDays"
            End If
            If vo.ResendTimesChg Then
                fieldCount += 1
                'Fields += ",ResendTimes"
                Parms += ",ResendTimes=@ResendTimes"
            End If

            If vo.ResendTimesNowChg Then
                fieldCount += 1
                'Fields += ",ResendTimesNow"
                Parms += ",ResendTimesNow=@ResendTimesNow"
            End If
            If vo.ResendDateChg Then
                fieldCount += 1
                ' Fields += ",ResendDate"
                Parms += ",ResendDate=@ResendDate"
            End If
            If vo.MailSubjectChg Then
                fieldCount += 1
                '  Fields += ",MailSubject"
                Parms += ",MailSubject=@MailSubject"
            End If
            If vo.MailBodyChg Then
                fieldCount += 1
                'Fields += ",MailBody"
                Parms += ",MailBody=@MailBody"
            End If
            If vo.ReturnDeadLineNowChg Then
                fieldCount += 1
                '  Fields += ",ReturnDeadLineNow"
                Parms += ",ReturnDeadLineNow=@ReturnDeadLineNow"
            End If
            If vo.storehouseChg Then
                fieldCount += 1
                'Fields += ",storehouse"
                Parms += ",storehouse=@storehouse"
            End If
            If vo.ModiUIDChg Then
                fieldCount += 1
                'Fields += ",ModiUID"
                Parms += ",ModiUID=@ModiUID"
            End If

            If vo.ModiDateChg Then
                fieldCount += 1
                'Fields += ",ModiDate"
                Parms += ",ModiDate=@ModiDate"
            End If
            If vo.Asset_CostChg Then
                fieldCount += 1
                'Fields += ",Asset_Cost"
                Parms += ",Asset_Cost=@Asset_Cost"
            End If
            If vo.CrtUIDChg Then
                fieldCount += 1
                'Fields += ",CrtUID"
                Parms += ",CrtUID=@CrtUID"
            End If

            If vo.CrtDateChg Then
                fieldCount += 1
                '  Fields += ",CrtDate"
                Parms += ",CrtDate=@CrtDate"
            End If
            If vo.StoreLocationChg Then
                fieldCount += 1
                'Fields += ",StoreLocation"
                Parms += ",StoreLocation=@StoreLocation"
            End If

            If vo.UsingType2Chg Then
                fieldCount += 1
                'Fields += ",UsingType2"
                Parms += ",UsingType2=@UsingType2"
            End If
            If vo.HouseIDChg Then
                fieldCount += 1
                ' Fields += ",HouseID"
                Parms += ",HouseID=@HouseID"
            End If

            If vo.OtherRemarkChg Then
                fieldCount += 1
                ' Fields += ",HouseID"
                Parms += ",OtherRemark=@OtherRemark"
            End If
            If vo.UsingDescChg Then
                fieldCount += 1
                ' Fields += ",HouseID"
                Parms += ",UsingDesc=@UsingDesc"
            End If
            Dim sqlParams As SqlParameter() = New SqlParameter(fieldCount) {}
            Dim sb As New StringBuilder()
            sb.AppendLine("update V_AST_ASSETALL  set  AppID=isnull(AppID,0)+1, " + Parms.Substring(1) + " where " + Fields)
            sqlParams(0) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, vo.asset_serialNo.Length)
            sqlParams(0).Value = vo.asset_serialNo

            fieldCount = 0
            If vo.assetdef_idChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@assetdef_id", SqlDbType.Int)
                sqlParams(fieldCount).Value = vo.assetdef_id
            End If

            If vo.asset_ownerChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@asset_owner", SqlDbType.NVarChar, vo.asset_owner.Length)
                sqlParams(fieldCount).Value = vo.asset_owner
            End If
            If vo.asset_statusChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@asset_status", SqlDbType.NVarChar, vo.asset_status.Length)
                sqlParams(fieldCount).Value = vo.asset_status
            End If
            If vo.asset_cdateChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@asset_cdate", SqlDbType.NVarChar, vo.asset_cdate.Length)
                sqlParams(fieldCount).Value = vo.asset_cdate
            End If
            If vo.ClassChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Class", SqlDbType.NVarChar, vo.Class1.Length)
                sqlParams(fieldCount).Value = vo.Class1
            End If
            If vo.AssetNumberChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@AssetNumber", SqlDbType.NVarChar, vo.AssetNumber.Length)
                sqlParams(fieldCount).Value = vo.AssetNumber
            End If
            If vo.OwneridChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Ownerid", SqlDbType.NVarChar, vo.Ownerid.Length)
                sqlParams(fieldCount).Value = vo.Ownerid
            End If
            If vo.EXtChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@EXt", SqlDbType.NVarChar, vo.EXt.Length)
                sqlParams(fieldCount).Value = vo.EXt
            End If
            If vo.User1Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@User1", SqlDbType.NVarChar, vo.User1.Length)
                sqlParams(fieldCount).Value = vo.User1
            End If

            If vo.DeptChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Dept", SqlDbType.NVarChar, vo.Dept.Length)
                sqlParams(fieldCount).Value = vo.Dept
            End If
            If vo.LocationChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Location", SqlDbType.NVarChar, vo.Location.Length)
                sqlParams(fieldCount).Value = vo.Location
            End If
            If vo.ComputerNameChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@ComputerName", SqlDbType.NVarChar, vo.ComputerName.Length)
                sqlParams(fieldCount).Value = vo.ComputerName
            End If
            If vo.ModelChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Model", SqlDbType.NVarChar, vo.Model.Length)
                sqlParams(fieldCount).Value = vo.Model
            End If
            If vo.CPUChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@CPU", SqlDbType.NVarChar, vo.CPU.Length)
                sqlParams(fieldCount).Value = vo.CPU
            End If
            If vo.FDDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@FDD", SqlDbType.NVarChar, vo.FDD.Length)
                sqlParams(fieldCount).Value = vo.FDD
            End If
            If vo.CD_RChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@CD_R", SqlDbType.NVarChar, vo.CD_R.Length)
                sqlParams(fieldCount).Value = vo.CD_R
            End If
            If vo.CD_RWChg Then
                fieldCount += 1

                sqlParams(fieldCount) = New SqlParameter("@CD_RW", SqlDbType.NVarChar, vo.CD_RW.Length)
                sqlParams(fieldCount).Value = vo.CD_RW

            End If

            If vo.MAC1Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MAC1", SqlDbType.NVarChar, vo.MAC1.Length)
                sqlParams(fieldCount).Value = vo.MAC1
            End If
            If vo.MAC2Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MAC2", SqlDbType.NVarChar, vo.MAC2.Length)
                sqlParams(fieldCount).Value = vo.MAC2
            End If
            '========================
            If vo.PSChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@PS", SqlDbType.NVarChar, vo.PS.Length)
                sqlParams(fieldCount).Value = vo.PS
            End If
            If vo.NBLabelChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@NBLabel", SqlDbType.NVarChar, vo.NBLabel.Length)
                sqlParams(fieldCount).Value = vo.NBLabel
            End If

            If vo.MailChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Mail", SqlDbType.NVarChar, vo.Mail.Length)
                sqlParams(fieldCount).Value = vo.Mail
            End If
            If vo.RemarkChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Remark", SqlDbType.NVarChar, vo.Remark.Length)
                sqlParams(fieldCount).Value = vo.Remark
            End If
            If vo.OSChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@OS", SqlDbType.NVarChar, vo.OS.Length)
                sqlParams(fieldCount).Value = vo.OS
            End If
            If vo.HDDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@HDD", SqlDbType.NVarChar, vo.HDD.Length)
                sqlParams(fieldCount).Value = vo.HDD
            End If

            If vo.RAMChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@RAM", SqlDbType.NVarChar, vo.RAM.Length)
                sqlParams(fieldCount).Value = vo.RAM
            End If
            If vo.UsingTypeChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@UsingType", SqlDbType.NVarChar, vo.UsingType.Length)
                sqlParams(fieldCount).Value = vo.UsingType
            End If
            If vo.MonitorSizeChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MonitorSize", SqlDbType.NVarChar, vo.MonitorSize.Length)
                sqlParams(fieldCount).Value = vo.MonitorSize
            End If
            If vo.FactoryChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Factory", SqlDbType.NVarChar, vo.Factory.Length)
                sqlParams(fieldCount).Value = vo.Factory
            End If
            If vo.TEST11Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@TEST11", SqlDbType.NVarChar, vo.TEST11.Length)
                sqlParams(fieldCount).Value = vo.TEST11
            End If

            If vo.test456Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@test456", SqlDbType.NVarChar, vo.test456.Length)
                sqlParams(fieldCount).Value = vo.test456
            End If
            If vo.UserNameChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@UserName", SqlDbType.NVarChar, vo.UserName.Length)
                sqlParams(fieldCount).Value = vo.UserName
            End If
            If vo.User2Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@User2", SqlDbType.NVarChar, vo.User1.Length)
                sqlParams(fieldCount).Value = vo.User2
            End If
            If vo.ReturnDateChg Then
                fieldCount += 1
                If vo.ReturnDate <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ReturnDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.ReturnDate
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ReturnDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If

            End If
            If vo.ReturnByChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@ReturnBy", SqlDbType.NVarChar, vo.ReturnBy.Length)
                sqlParams(fieldCount).Value = vo.ReturnBy

            End If
            If vo.ReturnDeadLineChg Then
                fieldCount += 1
                If vo.ReturnDeadLine <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ReturnDeadLine", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.ReturnDeadLine
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ReturnDeadLine", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If

            If vo.ResendDaysChg Then
                fieldCount += 1
                If vo.ResendDays <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ResendDays", SqlDbType.Float)
                    sqlParams(fieldCount).Value = vo.ResendDays
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ResendDays", SqlDbType.Float)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.ResendTimesChg Then
                fieldCount += 1
                If vo.ResendTimes <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ResendTimes", SqlDbType.Float)
                    sqlParams(fieldCount).Value = vo.ResendTimes
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ResendTimes", SqlDbType.Float)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If

            If vo.ResendTimesNowChg Then
                fieldCount += 1
                If vo.ResendTimesNow <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ResendTimesNow", SqlDbType.Float)
                    sqlParams(fieldCount).Value = vo.ResendTimesNow
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ResendTimesNow", SqlDbType.Float)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.ResendDateChg Then
                fieldCount += 1
                If vo.ResendDate <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ResendDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.ResendDate
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ResendDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.MailSubjectChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MailSubject", SqlDbType.NVarChar, vo.MailSubject.Length)
                sqlParams(fieldCount).Value = vo.MailSubject
            End If
            If vo.MailBodyChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MailBody", SqlDbType.NVarChar, vo.MailBody.Length)
                sqlParams(fieldCount).Value = vo.MailBody
            End If
            If vo.ReturnDeadLineNowChg Then
                fieldCount += 1
                If vo.ReturnDeadLineNow <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ReturnDeadLineNow", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.ReturnDeadLineNow
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ReturnDeadLineNow", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.storehouseChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@storehouse", SqlDbType.NVarChar, vo.storehouse.Length)
                sqlParams(fieldCount).Value = vo.storehouse
            End If
            If vo.ModiUIDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@ModiUID", SqlDbType.NVarChar, vo.ModiUID.Length)
                sqlParams(fieldCount).Value = vo.ModiUID
            End If

            If vo.ModiDateChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@ModiDate", SqlDbType.NVarChar, vo.ModiDate.Length)
                sqlParams(fieldCount).Value = vo.ModiDate
            End If
            If vo.Asset_CostChg Then
                fieldCount += 1
                If vo.Asset_CostChg Then
                    fieldCount += 1
                    If vo.Asset_Cost <> "" Then
                        sqlParams(fieldCount) = New SqlParameter("@Asset_Cost", SqlDbType.Float)
                        sqlParams(fieldCount).Value = vo.Asset_Cost
                    Else

                        sqlParams(fieldCount) = New SqlParameter("@Asset_Cost", SqlDbType.Float)
                        sqlParams(fieldCount).Value = DBNull.Value
                    End If
                End If
            End If
            If vo.CrtUIDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@CrtUID", SqlDbType.NVarChar, vo.CrtUID.Length)
                sqlParams(fieldCount).Value = vo.CrtUID
            End If

            If vo.CrtDateChg Then
                fieldCount += 1
                If vo.CrtDate <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@CrtDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.CrtDate
                Else

                    sqlParams(fieldCount) = New SqlParameter("@CrtDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.StoreLocationChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@StoreLocation", SqlDbType.NVarChar, vo.StoreLocation.Length)
                sqlParams(fieldCount).Value = vo.StoreLocation
            End If

            If vo.UsingType2Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@UsingType2", SqlDbType.NVarChar, vo.UsingType2.Length)
                sqlParams(fieldCount).Value = vo.UsingType2
            End If
            If vo.HouseIDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@HouseID", SqlDbType.NVarChar, vo.HouseID.Length)
                sqlParams(fieldCount).Value = vo.HouseID
            End If
            If vo.OtherRemarkChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@OtherRemark", SqlDbType.NVarChar, vo.OtherRemark.Length)
                sqlParams(fieldCount).Value = vo.OtherRemark
            End If
            If vo.UsingDescChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@UsingDesc", SqlDbType.NVarChar, vo.UsingDesc.Length)
                sqlParams(fieldCount).Value = vo.UsingDesc
            End If
            InsertAssetLog(vo, "1", webPath)
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            If vo.asset_status <> "Distributed" Then ''不在使用中的資料,取消要回報的資料
                sb.Length = 0
                sb.AppendLine("update V_Ast_AssetAll  set  ReturnDate=null,AppID=isnull(AppID,0)+1 ,ReturnBy='',ReturnDeadLine=null,ResendDays=0 ,ResendTimes=0 ,ResendTimesNow=0,ResendDate=null,MailSubject='',MailBody='' ,SendDate=null,ParamID= null where asset_serialNo='" + vo.asset_serialNo + "'")
                SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString())

            End If


        End Sub




        Public Sub InsertAssetData(ByVal vo As AssetManage.VO.AssetVO, ByVal webPath As String)

            Dim fieldCount As Integer = 1
            Dim Fields = "asset_serialNo"
            Dim Parms = "@asset_serialNo"



            If vo.assetdef_idChg Then
                fieldCount += 1
                Fields += ",assetdef_id"
                Parms += ",@assetdef_id"
            End If

            If vo.asset_ownerChg Then
                fieldCount += 1
                Fields += ",asset_owner"
                Parms += ",@asset_owner"
            End If
            If vo.asset_statusChg Then
                fieldCount += 1
                Fields += ",asset_status"
                Parms += ",@asset_status"
            End If
            If vo.asset_cdateChg Then
                fieldCount += 1
                Fields += ",asset_cdate"
                Parms += ",@asset_cdate"
            End If
            If vo.ClassChg Then
                fieldCount += 1
                Fields += ",Class"
                Parms += ",@Class"
            End If
            If vo.AssetNumberChg Then
                fieldCount += 1
                Fields += ",AssetNumber"
                Parms += ",@AssetNumber"
            End If
            If vo.OwneridChg Then
                fieldCount += 1
                Fields += ",Ownerid"
                Parms += ",@Ownerid"
            End If
            If vo.EXtChg Then
                fieldCount += 1
                Fields += ",EXt"
                Parms += ",@EXt"
            End If
            If vo.User1Chg Then
                fieldCount += 1
                Fields += ",User1"
                Parms += ",@User1"
            End If

            If vo.DeptChg Then
                fieldCount += 1
                Fields += ",Dept"
                Parms += ",@Dept"
            End If
            If vo.LocationChg Then
                fieldCount += 1
                Fields += ",Location"
                Parms += ",@Location"
            End If
            If vo.ComputerNameChg Then
                fieldCount += 1
                Fields += ",ComputerName"
                Parms += ",@ComputerName"
            End If
            If vo.ModelChg Then
                fieldCount += 1
                Fields += ",Model"
                Parms += ",@Model"
            End If
            If vo.CPUChg Then
                fieldCount += 1
                Fields += ",CPU"
                Parms += ",@CPU"
            End If
            If vo.FDDChg Then
                fieldCount += 1
                Fields += ",FDD"
                Parms += ",@FDD"
            End If
            If vo.CD_RChg Then
                fieldCount += 1
                Fields += ",CD_R"
                Parms += ",@CD_R"
            End If
            If vo.CD_RWChg Then
                fieldCount += 1
                Fields += ",CD_RW"
                Parms += ",@CD_RW"
            End If

            If vo.MAC1Chg Then
                fieldCount += 1
                Fields += ",MAC1"
                Parms += ",@MAC1"
            End If
            If vo.MAC2Chg Then
                fieldCount += 1
                Fields += ",MAC2"
                Parms += ",@MAC2"
            End If
            If vo.PSChg Then
                fieldCount += 1
                Fields += ",PS"
                Parms += ",@PS"
            End If
            If vo.NBLabelChg Then
                fieldCount += 1
                Fields += ",NBLabel"
                Parms += ",@NBLabel"
            End If

            If vo.MailChg Then
                fieldCount += 1
                Fields += ",Mail"
                Parms += ",@Mail"
            End If
            If vo.RemarkChg Then
                fieldCount += 1
                Fields += ",Remark"
                Parms += ",@Remark"
            End If
            If vo.OSChg Then
                fieldCount += 1
                Fields += ",OS"
                Parms += ",@OS"
            End If
            If vo.HDDChg Then
                fieldCount += 1
                Fields += ",HDD"
                Parms += ",@HDD"
            End If

            If vo.RAMChg Then
                fieldCount += 1
                Fields += ",RAM"
                Parms += ",@RAM"
            End If
            If vo.UsingTypeChg Then
                fieldCount += 1
                Fields += ",UsingType"
                Parms += ",@UsingType"
            End If
            If vo.MonitorSizeChg Then
                fieldCount += 1
                Fields += ",MonitorSize"
                Parms += ",@MonitorSize"
            End If
            If vo.FactoryChg Then
                fieldCount += 1
                Fields += ",Factory"
                Parms += ",@Factory"
            End If
            If vo.TEST11Chg Then
                fieldCount += 1
                Fields += ",TEST11"
                Parms += ",@TEST11"
            End If

            If vo.test456Chg Then
                fieldCount += 1
                Fields += ",test456"
                Parms += ",@test456"
            End If
            If vo.UserNameChg Then
                fieldCount += 1
                Fields += ",UserName"
                Parms += ",@UserName"
            End If
            If vo.User2Chg Then
                fieldCount += 1
                Fields += ",User2"
                Parms += ",@User2"
            End If
            If vo.ReturnDateChg Then
                fieldCount += 1
                Fields += ",ReturnDate"
                Parms += ",@ReturnDate"
            End If
            If vo.ReturnByChg Then
                fieldCount += 1
                Fields += ",ReturnBy"
                Parms += ",@ReturnBy"
            End If
            If vo.ReturnDeadLineChg Then
                fieldCount += 1
                Fields += ",ReturnDeadLine"
                Parms += ",@ReturnDeadLine"
            End If

            If vo.ResendDaysChg Then
                fieldCount += 1
                Fields += ",ResendDays"
                Parms += ",@ResendDays"
            End If
            If vo.ResendTimesChg Then
                fieldCount += 1
                Fields += ",ResendTimes"
                Parms += ",@ResendTimes"
            End If

            If vo.ResendTimesNowChg Then
                fieldCount += 1
                Fields += ",ResendTimesNow"
                Parms += ",@ResendTimesNow"
            End If
            If vo.ResendDateChg Then
                fieldCount += 1
                Fields += ",ResendDate"
                Parms += ",@ResendDate"
            End If
            If vo.MailSubjectChg Then
                fieldCount += 1
                Fields += ",MailSubject"
                Parms += ",@MailSubject"
            End If
            If vo.MailBodyChg Then
                fieldCount += 1
                Fields += ",MailBody"
                Parms += ",@MailBody"
            End If
            If vo.ReturnDeadLineNowChg Then
                fieldCount += 1
                Fields += ",ReturnDeadLineNow"
                Parms += ",@ReturnDeadLineNow"
            End If
            If vo.storehouseChg Then
                fieldCount += 1
                Fields += ",storehouse"
                Parms += ",@storehouse"
            End If
            If vo.ModiUIDChg Then
                fieldCount += 1
                Fields += ",ModiUID"
                Parms += ",@ModiUID"
            End If

            If vo.ModiDateChg Then
                fieldCount += 1
                Fields += ",ModiDate"
                Parms += ",@ModiDate"
            End If
            If vo.Asset_CostChg Then
                fieldCount += 1
                Fields += ",Asset_Cost"
                Parms += ",@Asset_Cost"
            End If
            If vo.CrtUIDChg Then
                fieldCount += 1
                Fields += ",CrtUID"
                Parms += ",@CrtUID"
            End If

            If vo.CrtDateChg Then
                fieldCount += 1
                Fields += ",CrtDate"
                Parms += ",@CrtDate"
            End If
            If vo.StoreLocationChg Then
                fieldCount += 1
                Fields += ",StoreLocation"
                Parms += ",@StoreLocation"
            End If

            If vo.UsingType2Chg Then
                fieldCount += 1
                Fields += ",UsingType2"
                Parms += ",@UsingType2"
            End If
            If vo.HouseIDChg Then
                fieldCount += 1
                Fields += ",HouseID"
                Parms += ",@HouseID"
            End If

            If vo.OtherRemarkChg Then
                fieldCount += 1
                Fields += ",OtherRemark"
                Parms += ",@OtherRemark"
            End If
            If vo.UsingDescChg Then
                fieldCount += 1
                Fields += ",UsingDesc"
                Parms += ",@UsingDesc"
            End If
            Dim sqlParams As SqlParameter() = New SqlParameter(fieldCount) {}
            Dim sb As New StringBuilder()
            sb.AppendLine("insert into V_AST_ASSETALL (  AppID, " + Fields + ")values('1'," + Parms + " ) ")
            vo.asset_serialNo = GetSerialNo()
            sqlParams(0) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar)
            sqlParams(0).Value = vo.asset_serialNo
            fieldCount = 0
            If vo.assetdef_idChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@assetdef_id", SqlDbType.Int)
                sqlParams(fieldCount).Value = vo.assetdef_id
            End If

            If vo.asset_ownerChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@asset_owner", SqlDbType.NVarChar, vo.asset_owner.Length)
                sqlParams(fieldCount).Value = vo.asset_owner
            End If
            If vo.asset_statusChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@asset_status", SqlDbType.NVarChar, vo.asset_status.Length)
                sqlParams(fieldCount).Value = vo.asset_status
            End If
            If vo.asset_cdateChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@asset_cdate", SqlDbType.NVarChar, vo.asset_cdate.Length)
                sqlParams(fieldCount).Value = vo.asset_cdate
            End If
            If vo.ClassChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Class", SqlDbType.NVarChar, vo.Class1.Length)
                sqlParams(fieldCount).Value = vo.Class1
            End If
            If vo.AssetNumberChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@AssetNumber", SqlDbType.NVarChar, vo.AssetNumber.Length)
                sqlParams(fieldCount).Value = vo.AssetNumber
            End If
            If vo.OwneridChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Ownerid", SqlDbType.NVarChar, vo.Ownerid.Length)
                sqlParams(fieldCount).Value = vo.Ownerid
            End If
            If vo.EXtChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@EXt", SqlDbType.NVarChar, vo.EXt.Length)
                sqlParams(fieldCount).Value = vo.EXt
            End If
            If vo.User1Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@User1", SqlDbType.NVarChar, vo.User1.Length)
                sqlParams(fieldCount).Value = vo.User1
            End If

            If vo.DeptChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Dept", SqlDbType.NVarChar, vo.Dept.Length)
                sqlParams(fieldCount).Value = vo.Dept
            End If
            If vo.LocationChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Location", SqlDbType.NVarChar, vo.Location.Length)
                sqlParams(fieldCount).Value = vo.Location
            End If
            If vo.ComputerNameChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@ComputerName", SqlDbType.NVarChar, vo.ComputerName.Length)
                sqlParams(fieldCount).Value = vo.ComputerName
            End If
            If vo.ModelChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Model", SqlDbType.NVarChar, vo.Model.Length)
                sqlParams(fieldCount).Value = vo.Model
            End If
            If vo.CPUChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@CPU", SqlDbType.NVarChar, vo.CPU.Length)
                sqlParams(fieldCount).Value = vo.CPU
            End If
            If vo.FDDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@FDD", SqlDbType.NVarChar, vo.FDD.Length)
                sqlParams(fieldCount).Value = vo.FDD
            End If
            If vo.CD_RChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@CD_R", SqlDbType.NVarChar, vo.CD_R.Length)
                sqlParams(fieldCount).Value = vo.CD_R
            End If
            If vo.CD_RWChg Then
                fieldCount += 1

                sqlParams(fieldCount) = New SqlParameter("@CD_RW", SqlDbType.NVarChar, vo.CD_RW.Length)
                sqlParams(fieldCount).Value = vo.CD_RW

            End If

            If vo.MAC1Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MAC1", SqlDbType.NVarChar, vo.MAC1.Length)
                sqlParams(fieldCount).Value = vo.MAC1
            End If
            If vo.MAC2Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MAC2", SqlDbType.NVarChar, vo.MAC2.Length)
                sqlParams(fieldCount).Value = vo.MAC2
            End If
            '========================
            If vo.PSChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@PS", SqlDbType.NVarChar, vo.PS.Length)
                sqlParams(fieldCount).Value = vo.PS
            End If
            If vo.NBLabelChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@NBLabel", SqlDbType.NVarChar, vo.NBLabel.Length)
                sqlParams(fieldCount).Value = vo.NBLabel
            End If

            If vo.MailChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Mail", SqlDbType.NVarChar, vo.Mail.Length)
                sqlParams(fieldCount).Value = vo.Mail
            End If
            If vo.RemarkChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Remark", SqlDbType.NVarChar, vo.Remark.Length)
                sqlParams(fieldCount).Value = vo.Remark
            End If
            If vo.OSChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@OS", SqlDbType.NVarChar, vo.OS.Length)
                sqlParams(fieldCount).Value = vo.OS
            End If
            If vo.HDDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@HDD", SqlDbType.NVarChar, vo.HDD.Length)
                sqlParams(fieldCount).Value = vo.HDD
            End If

            If vo.RAMChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@RAM", SqlDbType.NVarChar, vo.RAM.Length)
                sqlParams(fieldCount).Value = vo.RAM
            End If
            If vo.UsingTypeChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@UsingType", SqlDbType.NVarChar, vo.UsingType.Length)
                sqlParams(fieldCount).Value = vo.UsingType
            End If
            If vo.MonitorSizeChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MonitorSize", SqlDbType.NVarChar, vo.MonitorSize.Length)
                sqlParams(fieldCount).Value = vo.MonitorSize
            End If
            If vo.FactoryChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@Factory", SqlDbType.NVarChar, vo.Factory.Length)
                sqlParams(fieldCount).Value = vo.Factory
            End If
            If vo.TEST11Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@TEST11", SqlDbType.NVarChar, vo.TEST11.Length)
                sqlParams(fieldCount).Value = vo.TEST11
            End If

            If vo.test456Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@test456", SqlDbType.NVarChar, vo.test456.Length)
                sqlParams(fieldCount).Value = vo.test456
            End If
            If vo.UserNameChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@UserName", SqlDbType.NVarChar, vo.UserName.Length)
                sqlParams(fieldCount).Value = vo.UserName
            End If
            If vo.User2Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@User2", SqlDbType.NVarChar, vo.User1.Length)
                sqlParams(fieldCount).Value = vo.User2
            End If
            If vo.ReturnDateChg Then
                fieldCount += 1
                If vo.ReturnDate <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ReturnDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.ReturnDate
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ReturnDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If

            End If
            If vo.ReturnByChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@ReturnBy", SqlDbType.NVarChar, vo.ReturnBy.Length)
                sqlParams(fieldCount).Value = vo.ReturnBy

            End If
            If vo.ReturnDeadLineChg Then
                fieldCount += 1
                If vo.ReturnDeadLine <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ReturnDeadLine", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.ReturnDeadLine
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ReturnDeadLine", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If

            If vo.ResendDaysChg Then
                fieldCount += 1
                If vo.ResendDays <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ResendDays", SqlDbType.Float)
                    sqlParams(fieldCount).Value = vo.ResendDays
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ResendDays", SqlDbType.Float)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.ResendTimesChg Then
                fieldCount += 1
                If vo.ResendTimes <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ResendTimes", SqlDbType.Float)
                    sqlParams(fieldCount).Value = vo.ResendTimes
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ResendTimes", SqlDbType.Float)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If

            If vo.ResendTimesNowChg Then
                fieldCount += 1
                If vo.ResendTimesNow <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ResendTimesNow", SqlDbType.Float)
                    sqlParams(fieldCount).Value = vo.ResendTimesNow
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ResendTimesNow", SqlDbType.Float)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.ResendDateChg Then
                fieldCount += 1
                If vo.ResendDate <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ResendDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.ResendDate
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ResendDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.MailSubjectChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MailSubject", SqlDbType.NVarChar, vo.MailSubject.Length)
                sqlParams(fieldCount).Value = vo.MailSubject
            End If
            If vo.MailBodyChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@MailBody", SqlDbType.NVarChar, vo.MailBody.Length)
                sqlParams(fieldCount).Value = vo.MailBody
            End If
            If vo.ReturnDeadLineNowChg Then
                fieldCount += 1
                If vo.ReturnDeadLineNow <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@ReturnDeadLineNow", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.ReturnDeadLineNow
                Else

                    sqlParams(fieldCount) = New SqlParameter("@ReturnDeadLineNow", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.storehouseChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@storehouse", SqlDbType.NVarChar, vo.storehouse.Length)
                sqlParams(fieldCount).Value = vo.storehouse
            End If
            If vo.ModiUIDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@ModiUID", SqlDbType.NVarChar, vo.ModiUID.Length)
                sqlParams(fieldCount).Value = vo.ModiUID
            End If

            If vo.ModiDateChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@ModiDate", SqlDbType.NVarChar, vo.ModiDate.Length)
                sqlParams(fieldCount).Value = vo.ModiDate
            End If
            If vo.Asset_CostChg Then
                fieldCount += 1
                If vo.Asset_CostChg Then
                    fieldCount += 1
                    If vo.Asset_Cost <> "" Then
                        sqlParams(fieldCount) = New SqlParameter("@Asset_Cost", SqlDbType.Float)
                        sqlParams(fieldCount).Value = vo.Asset_Cost
                    Else

                        sqlParams(fieldCount) = New SqlParameter("@Asset_Cost", SqlDbType.Float)
                        sqlParams(fieldCount).Value = DBNull.Value
                    End If
                End If
            End If
            If vo.CrtUIDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@CrtUID", SqlDbType.NVarChar, vo.CrtUID.Length)
                sqlParams(fieldCount).Value = vo.CrtUID
            End If

            If vo.CrtDateChg Then
                fieldCount += 1
                If vo.CrtDate <> "" Then
                    sqlParams(fieldCount) = New SqlParameter("@CrtDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = vo.CrtDate
                Else

                    sqlParams(fieldCount) = New SqlParameter("@CrtDate", SqlDbType.DateTime)
                    sqlParams(fieldCount).Value = DBNull.Value
                End If
            End If
            If vo.StoreLocationChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@StoreLocation", SqlDbType.NVarChar, vo.StoreLocation.Length)
                sqlParams(fieldCount).Value = vo.StoreLocation
            End If

            If vo.UsingType2Chg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@UsingType2", SqlDbType.NVarChar, vo.UsingType2.Length)
                sqlParams(fieldCount).Value = vo.UsingType2
            End If
            If vo.HouseIDChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@HouseID", SqlDbType.NVarChar, vo.HouseID.Length)
                sqlParams(fieldCount).Value = vo.HouseID
            End If
            If vo.OtherRemarkChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@OtherRemark", SqlDbType.NVarChar, vo.OtherRemark.Length)
                sqlParams(fieldCount).Value = vo.OtherRemark
            End If
            If vo.UsingDescChg Then
                fieldCount += 1
                sqlParams(fieldCount) = New SqlParameter("@UsingDesc", SqlDbType.NVarChar, vo.UsingDesc.Length)
                sqlParams(fieldCount).Value = vo.UsingDesc
            End If

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            InsertAssetLog(vo, "0", webPath + vo.asset_serialNo)


        End Sub

        Public Sub InsertAssetLog(ByVal vo As AssetManage.VO.AssetVO, ByVal ModiType As String, ByVal webPath As String)
            Dim doChgOwner As Boolean = False
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            Dim ownerOld As String = ""

            sb.AppendLine("select * from  V_AST_ASSETALL  where asset_serialNo=@asset_serialNo ")
            '===========================.



            sqlParams(0) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, vo.asset_serialNo.Length)
            sqlParams(0).Value = vo.asset_serialNo

            Dim dr As DataRow = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows(0)
            ''新增到mySql中
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim sb1 As New StringBuilder()


            Try
                sb1.AppendLine(" select max(SeqNo)+1 as MAX1 from v_ast_assetalllog where asset_serialNo=?asset_serialNo ")

                Dim oTable As Data.DataTable

                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", vo.asset_serialNo)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 1
                End If
                con.Open()
                sb1.Length = 0
                Dim field As String = "asset_serialNo, ModiType, SeqNo,ModiUID,ModiDate"
                Dim field1 As String = "?asset_serialNo,?ModiType,?SeqNo,?ModiUID,?ModiDate"

                If vo.assetdef_idChg Then
                    field += ",assetdef_id"
                    field1 += ",?assetdef_id"
                End If
                If vo.asset_ownerChg Then
                    field += ",asset_owner"
                    field1 += ",?asset_owner"
                End If
                If vo.asset_statusChg Then
                    field += ",asset_status"
                    field1 += ",?asset_status"
                End If

                'If dr("asset_cdate").ToString.Trim <> vo.asset_cdate.Trim() Then
                '    field += ",asset_cdate"
                '    field1 += ",?asset_cdate"
                'End If

                If vo.ClassChg Then
                    field += ",Class"
                    field1 += ",?Class"
                End If


                'If dr("AssetNumber").ToString.Trim <> vo.AssetNumber.Trim() Then
                '    field += ",AssetNumber"
                '    field1 += ",?AssetNumber"
                'End If
                'If dr("Ownerid").ToString.Trim <> vo.Ownerid.Trim() Then
                '    field += ",Ownerid"
                '    field1 += ",?Ownerid"
                'End If
                If vo.EXtChg Then
                    field += ",EXt"
                    field1 += ",?EXt"
                End If
                If vo.User1Chg Then
                    field += ",User1"
                    field1 += ",?User1"
                End If
                If vo.DeptChg Then
                    field += ",Dept"
                    field1 += ",?Dept"
                End If
                If vo.LocationChg Then
                    field += ",Location"
                    field1 += ",?Location"
                End If

                If vo.ComputerNameChg Then
                    field += ",ComputerName"
                    field1 += ",?ComputerName"
                End If

                If vo.ModelChg Then
                    field += ",Model"
                    field1 += ",?Model"
                End If
                If vo.CPUChg Then
                    field += ",CPU"
                    field1 += ",?CPU"
                End If


                If vo.FDDChg Then
                    field += ",FDD"
                    field1 += ",?FDD"
                End If


                If vo.CD_RChg Then
                    field += ",CD_R"
                    field1 += ",?CD_R"
                End If

                If vo.CD_RWChg Then
                    field += ",CD_RW"
                    field1 += ",?CD_RW"
                End If
                If vo.MAC1Chg Then
                    field += ",MAC1"
                    field1 += ",?MAC1"
                End If
                If vo.MAC2Chg Then
                    field += ",MAC2"
                    field1 += ",?MAC2"
                End If
                If vo.PSChg Then
                    field += ",PS"
                    field1 += ",?PS"
                End If

                If vo.NBLabelChg Then
                    field += ",NBLabel"
                    field1 += ",?NBLabel"
                End If
                'If dr("Mail").ToString.Trim <> vo.Mail.Trim() Then
                '    field += ",Mail"
                '    field1 += ",?Mail"
                'End If
                If vo.RemarkChg Then
                    field += ",Remark"
                    field1 += ",?Remark"
                End If
                If vo.OSChg Then
                    field += ",OS"
                    field1 += ",?OS"
                End If
                If vo.HDDChg Then
                    field += ",HDD"
                    field1 += ",?HDD"
                End If
                If vo.RAMChg Then
                    field += ",RAM"
                    field1 += ",?RAM"
                End If
                If vo.UsingTypeChg Then
                    field += ",UsingType"
                    field1 += ",?UsingType"
                End If

                If vo.MonitorSizeChg Then
                    field += ",MonitorSize"
                    field1 += ",?MonitorSize"
                End If
                If vo.FactoryChg Then
                    field += ",Factory"
                    field1 += ",?Factory"
                End If
                'If dr("TEST11").ToString.Trim <> vo.TEST11.Trim() Then
                '    field += ",TEST11"
                '    field1 += ",?TEST11"
                'End If
                'If dr("test456").ToString.Trim <> vo.test456.Trim() Then
                '    field += ",test456"
                '    field1 += ",?test456"
                'End If
                'If dr("UserName").ToString.Trim <> vo.UserName.Trim() Then
                '    field += ",UserName"
                '    field1 += ",?UserName"
                'End If
                'If dr("User1").ToString.Trim <> vo.User1.Trim() Then
                '    field += ",User1"
                '    field1 += ",?User1"
                'End If
                'If dr("ReturnDate").ToString.Trim <> vo.ReturnDate.Trim() Then
                '    field += ",ReturnDate"
                '    field1 += ",?ReturnDate"
                'End If
                'If dr("ReturnBy").ToString.Trim <> vo.ReturnBy.Trim() Then
                '    field += ",ReturnBy"
                '    field1 += ",?ReturnBy"
                'End If
                'If dr("ReturnDeadLine").ToString.Trim <> vo.ReturnDeadLine.Trim() Then
                '    field += ",ReturnDeadLine"
                '    field1 += ",?ReturnDeadLine"
                'End If
                'If dr("ResendDays").ToString.Trim <> vo.ResendDays.Trim() Then
                '    field += ",ResendDays"
                '    field1 += ",?ResendDays"
                'End If
                'If dr("ResendTimes").ToString.Trim <> vo.ResendTimes.Trim() Then
                '    field += ",ResendTimes"
                '    field1 += ",?ResendTimes"
                'End If
                'If dr("ResendTimesNow").ToString.Trim <> vo.ResendTimesNow.Trim() Then
                '    field += ",ResendTimesNow"
                '    field1 += ",?ResendTimesNow"
                'End If
                'If dr("ResendDate").ToString.Trim <> vo.ResendDate.Trim() Then
                '    field += ",ResendDate"
                '    field1 += ",?ResendDate"
                'End If

                'If dr("MailSubject").ToString.Trim <> vo.MailSubject.Trim() Then
                '    field += ",MailSubject"
                '    field1 += ",?MailSubject"
                'End If
                'If dr("MailBody").ToString.Trim <> vo.MailBody.Trim() Then
                '    field += ",MailBody"
                '    field1 += ",?MailBody"
                'End If
                'If dr("ReturnDeadLineNow").ToString.Trim <> vo.ReturnDeadLineNow.Trim() Then
                '    field += ",ReturnDeadLineNow"
                '    field1 += ",?ReturnDeadLineNow"
                'End If
                If vo.storehouseChg Then
                    field += ",storehouse"
                    field1 += ",?storehouse"
                End If


                'If dr("CrtUID").ToString.Trim <> vo.CrtUID.Trim() Then
                '    field += ",CrtUID"
                '    field1 += ",?CrtUID"
                'End If

                'If dr("CrtDate").ToString.Trim <> vo.CrtDate.Trim() Then
                '    field += ",CrtDate"
                '    field1 += ",?CrtDate"
                'End If


                If vo.StoreLocationChg Then
                    field += ",StoreLocation"
                    field1 += ",?StoreLocation"
                End If

                If vo.UsingType2Chg Then
                    field += ",UsingType2"
                    field1 += ",?UsingType2"
                End If

                If vo.HouseIDChg Then
                    field += ",HouseID"
                    field1 += ",?HouseID"
                End If
                If vo.OtherRemarkChg Then
                    field += ",OtherRemark"
                    field1 += ",?OtherRemark"
                End If
                If vo.Asset_CostChg Then
                    field += ",Asset_Cost"
                    field1 += ",?Asset_Cost"
                End If
                If vo.UsingDescChg Then
                    field += ",UsingDesc"
                    field1 += ",?UsingDesc"
                End If
                ''=================================================5/10
                If dr("assetdef_id").ToString.Trim <> vo.assetdef_id.Trim Then
                    field += ",assetdef_idChg"
                    field1 += ",?assetdef_idChg"
                End If
                If dr("asset_owner").ToString.Trim <> vo.asset_owner.Trim Then
                    field += ",asset_ownerChg"
                    field1 += ",?asset_ownerChg"
                    doChgOwner = True
                    ownerOld = dr("asset_owner").ToString.Trim()
                    If ModiType = "1" Then
                        ModiType = "2"
                    End If
                End If
                If dr("asset_status").ToString.Trim <> vo.asset_status.Trim Then
                    field += ",asset_statusChg"
                    field1 += ",?asset_statusChg"
                End If



                If dr("Class").ToString.Trim <> vo.Class1.Trim Then
                    field += ",ClassChg"
                    field1 += ",?ClassChg"
                End If



                If dr("EXt").ToString.Trim <> vo.EXt.Trim Then
                    field += ",EXtChg"
                    field1 += ",?EXtChg"
                End If
                If dr("User1").ToString.Trim <> vo.User1.Trim Then
                    field += ",User1Chg"
                    field1 += ",?User1Chg"
                End If
                If dr("Dept").ToString.Trim <> vo.Dept.Trim Then
                    field += ",DeptChg"
                    field1 += ",?DeptChg"
                End If
                If dr("Location").ToString.Trim <> vo.Location.Trim Then
                    field += ",LocationChg"
                    field1 += ",?LocationChg"
                End If

                If dr("ComputerName").ToString.Trim <> vo.ComputerName.Trim Then
                    field += ",ComputerNameChg"
                    field1 += ",?ComputerNameChg"
                End If

                If dr("Model").ToString.Trim <> vo.Model.Trim Then
                    field += ",ModelChg"
                    field1 += ",?ModelChg"
                End If
                If dr("CPU").ToString.Trim <> vo.CPU.Trim Then
                    field += ",CPUChg"
                    field1 += ",?CPUChg"
                End If


                If dr("FDD").ToString.Trim <> vo.FDD.Trim Then
                    field += ",FDDChg"
                    field1 += ",?FDDChg"
                End If


                If dr("CD_R").ToString.Trim <> vo.CD_R.Trim Then
                    field += ",CD_RChg"
                    field1 += ",?CD_RChg"
                End If

                If dr("CD_RW").ToString.Trim <> vo.CD_RW.Trim Then
                    field += ",CD_RWChg"
                    field1 += ",?CD_RWChg"
                End If
                If dr("MAC1").ToString.Trim <> vo.MAC1.Trim Then
                    field += ",MAC1Chg"
                    field1 += ",?MAC1Chg"
                End If
                If dr("MAC2").ToString.Trim <> vo.MAC2.Trim Then
                    field += ",MAC2Chg"
                    field1 += ",?MAC2Chg"
                End If
                If dr("PS").ToString.Trim <> vo.PS.Trim Then
                    field += ",PSChg"
                    field1 += ",?PSChg"
                End If

                If dr("NBLabel").ToString.Trim <> vo.NBLabel.Trim Then
                    field += ",NBLabelChg"
                    field1 += ",?NBLabelChg"
                End If

                If dr("Remark").ToString.Trim <> vo.Remark.Trim Then
                    field += ",RemarkChg"
                    field1 += ",?RemarkChg"
                End If
                If dr("OS").ToString.Trim <> vo.OS.Trim Then
                    field += ",OSChg"
                    field1 += ",?OSChg"
                End If
                If dr("HDD").ToString.Trim <> vo.HDD.Trim Then
                    field += ",HDDChg"
                    field1 += ",?HDDChg"
                End If
                If dr("RAM").ToString.Trim <> vo.RAM.Trim Then
                    field += ",RAMChg"
                    field1 += ",?RAMChg"
                End If
                If dr("UsingType").ToString.Trim <> vo.UsingType.Trim Then
                    field += ",UsingTypeChg"
                    field1 += ",?UsingTypeChg"
                End If

                If dr("MonitorSize").ToString.Trim <> vo.MonitorSize.Trim Then
                    field += ",MonitorSizeChg"
                    field1 += ",?MonitorSizeChg"
                End If
                If dr("Factory").ToString.Trim <> vo.Factory.Trim Then
                    field += ",FactoryChg"
                    field1 += ",?FactoryChg"
                End If

                If dr("storehouse").ToString.Trim <> vo.storehouse.Trim Then
                    field += ",storehouseChg"
                    field1 += ",?storehouseChg"
                End If

                If dr("StoreLocation").ToString.Trim <> vo.StoreLocation.Trim Then
                    field += ",StoreLocationChg"
                    field1 += ",?StoreLocationChg"
                End If

                If dr("UsingType2").ToString.Trim <> vo.UsingType2.Trim Then
                    field += ",UsingType2Chg"
                    field1 += ",?UsingType2Chg"
                End If

                If dr("HouseID").ToString.Trim <> vo.HouseID.Trim Then
                    field += ",HouseIDChg"
                    field1 += ",?HouseIDChg"
                End If
                If dr("OtherRemark").ToString.Trim <> vo.OtherRemark.Trim Then
                    field += ",OtherRemarkChg"
                    field1 += ",?OtherRemarkChg"
                End If
                If dr("Asset_Cost").ToString.Trim <> vo.Asset_Cost.Trim Then
                    field += ",Asset_CostChg"
                    field1 += ",?Asset_CostChg"
                End If

                If dr("UsingDesc").ToString.Trim <> vo.UsingDesc.Trim Then
                    field += ",UsingDescChg"
                    field1 += ",?UsingDescChg"
                End If
                'If doChgOwner Then
                field += ",FromDept,Fromasset_owner"
                field1 += ",?FromDept,?Fromasset_owner"
                'End If
                sb1.AppendLine(" insert into v_ast_assetalllog (" + field + ") values (" + field1 + ");")

                sb1.AppendLine("Update v_ast_assetalllog  set HouseID=?HouseID  where asset_serialNo=?asset_serialNo;")
                If vo.asset_status = "Scrapped" Then
                    sb1.AppendLine("Update v_ast_assetalllog  set isScrapped='1' where asset_serialNo=?asset_serialNo and ModiType='4';")
                End If
                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", vo.asset_serialNo)

                If ModiType = "1" Or ModiType = "2" Then
                    If doChgOwner Then
                        If vo.asset_status = "Scrapped" Or dr("asset_status").ToString = "Scrapping" Then
                            cmd.Parameters.AddWithValue("?ModiType", "7")
                        Else
                            If vo.asset_status = "Storing" Then
                                cmd.Parameters.AddWithValue("?ModiType", "4")
                            Else
                                cmd.Parameters.AddWithValue("?ModiType", "2")
                            End If

                        End If

                    Else
                        If vo.asset_status = "Scrapped" Or dr("asset_status").ToString = "Scrapping" Then
                            cmd.Parameters.AddWithValue("?ModiType", "7")
                        Else
                            If vo.asset_status = "Storing" Then
                                cmd.Parameters.AddWithValue("?ModiType", "4")
                            Else
                                cmd.Parameters.AddWithValue("?ModiType", "1")
                            End If
                        End If
                    End If
                Else
                    If doChgOwner Then
                        If vo.asset_status = "Storing" Then
                            cmd.Parameters.AddWithValue("?ModiType", "4")
                        Else
                            cmd.Parameters.AddWithValue("?ModiType", "2")
                        End If
                    Else
                        If vo.asset_status = "Storing" Then
                            cmd.Parameters.AddWithValue("?ModiType", "4")
                        Else
                            cmd.Parameters.AddWithValue("?ModiType", ModiType)
                        End If
                    End If
                End If


                cmd.Parameters.AddWithValue("?SeqNo", max)
                cmd.Parameters.AddWithValue("?ModiUID", vo.ModiUID)
                cmd.Parameters.AddWithValue("?ModiDate", DateTime.Now)

                If vo.assetdef_idChg Then
                    cmd.Parameters.AddWithValue("?assetdef_id", vo.assetdef_id)
                End If
                If vo.asset_ownerChg Then
                    cmd.Parameters.AddWithValue("?asset_owner", vo.asset_owner)
                End If
                If vo.asset_statusChg Then

                    cmd.Parameters.AddWithValue("?asset_status", vo.asset_status)
                End If

                'If dr("asset_cdate").ToString.Trim <> vo.asset_cdate.Trim() Then
                '    cmd.Parameters.AddWithValue("?asset_cdate", vo.asset_cdate)
                'End If

                If vo.ClassChg Then
                    cmd.Parameters.AddWithValue("?Class", vo.Class1)
                End If


                'If dr("AssetNumber").ToString.Trim <> vo.AssetNumber.Trim() Then
                '    cmd.Parameters.AddWithValue("?AssetNumber", vo.AssetNumber)
                'End If
                'If dr("Ownerid").ToString.Trim <> vo.Ownerid.Trim() Then
                '    cmd.Parameters.AddWithValue("?Ownerid", vo.Ownerid)
                'End If
                If vo.EXtChg Then
                    cmd.Parameters.AddWithValue("?EXt", vo.EXt)
                End If
                If vo.User1Chg Then
                    cmd.Parameters.AddWithValue("?User1", vo.User1)
                End If
                '========================
                If vo.DeptChg Then
                    cmd.Parameters.AddWithValue("?Dept", vo.Dept)
                End If
                If vo.LocationChg Then
                    cmd.Parameters.AddWithValue("?Location", vo.Location)
                End If

                If vo.ComputerNameChg Then
                    cmd.Parameters.AddWithValue("?ComputerName", vo.ComputerName)
                End If

                If vo.ModelChg Then
                    cmd.Parameters.AddWithValue("?Model", vo.Model)
                End If
                If vo.CPUChg Then
                    cmd.Parameters.AddWithValue("?CPU", vo.CPU)
                End If


                If vo.FDDChg Then
                    cmd.Parameters.AddWithValue("?FDD", vo.FDD)
                End If


                If vo.CD_RChg Then
                    cmd.Parameters.AddWithValue("?CD_R", vo.CD_R)
                End If

                If vo.CD_RWChg Then
                    cmd.Parameters.AddWithValue("?CD_RW", vo.CD_RW)
                End If
                If vo.MAC1Chg Then
                    cmd.Parameters.AddWithValue("?MAC1", vo.MAC1)
                End If
                If vo.MAC2Chg Then
                    cmd.Parameters.AddWithValue("?MAC2", vo.MAC2)
                End If
                If vo.PSChg Then
                    cmd.Parameters.AddWithValue("?PS", vo.PS)
                End If

                If vo.NBLabelChg Then
                    cmd.Parameters.AddWithValue("?NBLabel", vo.NBLabel)
                End If
                If vo.MailChg Then
                    cmd.Parameters.AddWithValue("?Mail", vo.Mail)
                End If
                If vo.RemarkChg Then
                    cmd.Parameters.AddWithValue("?Remark", vo.Remark)
                End If
                If vo.OSChg Then
                    cmd.Parameters.AddWithValue("?OS", vo.OS)
                End If
                If vo.HDDChg Then
                    cmd.Parameters.AddWithValue("?HDD", vo.HDD)
                End If
                If vo.RAMChg Then
                    cmd.Parameters.AddWithValue("?RAM", vo.RAM)
                End If
                If vo.UsingTypeChg Then
                    cmd.Parameters.AddWithValue("?UsingType", vo.UsingType)
                End If

                If vo.MonitorSizeChg Then
                    cmd.Parameters.AddWithValue("?MonitorSize", vo.MonitorSize)
                End If
                If vo.FactoryChg Then
                    cmd.Parameters.AddWithValue("?Factory", vo.Factory)
                End If

                If vo.storehouseChg Then
                    cmd.Parameters.AddWithValue("?storehouse", vo.storehouse)
                    field1 += ",?storehouse"
                End If
                If vo.StoreLocationChg Then
                    cmd.Parameters.AddWithValue("?StoreLocation", vo.StoreLocation)
                End If

                If vo.UsingType2Chg Then
                    cmd.Parameters.AddWithValue("?UsingType2", vo.UsingType2)
                End If

                If vo.HouseIDChg Then
                    cmd.Parameters.AddWithValue("?HouseID", vo.HouseID)
                End If

                If vo.OtherRemarkChg Then
                    cmd.Parameters.AddWithValue("?OtherRemark", vo.OtherRemark)
                End If
                If vo.Asset_CostChg Then
                    If vo.Asset_Cost.Trim = "" Then

                        cmd.Parameters.AddWithValue("?Asset_Cost", DBNull.Value)
                    Else

                        cmd.Parameters.AddWithValue("?Asset_Cost", vo.Asset_Cost)
                    End If

                End If
                If vo.UsingDescChg Then
                    cmd.Parameters.AddWithValue("?UsingDesc", vo.UsingDesc)
                End If

                ''=================================================5/10
                If dr("assetdef_id").ToString.Trim <> vo.assetdef_id.Trim Then

                    cmd.Parameters.AddWithValue("?assetdef_idChg", "Y")
                End If
                If dr("asset_owner").ToString.Trim <> vo.asset_owner.Trim Then
                    cmd.Parameters.AddWithValue("?asset_ownerChg", "Y")
                End If
                If dr("asset_status").ToString.Trim <> vo.asset_status.Trim Then

                    cmd.Parameters.AddWithValue("?asset_statusChg", "Y")
                End If



                If dr("Class").ToString.Trim <> vo.Class1.Trim Then
                    cmd.Parameters.AddWithValue("?ClassChg", "Y")
                End If



                If dr("EXt").ToString.Trim <> vo.EXt.Trim Then

                    cmd.Parameters.AddWithValue("?EXtChg", "Y")
                End If
                If dr("User1").ToString.Trim <> vo.User1.Trim Then

                    cmd.Parameters.AddWithValue("?User1Chg", "Y")
                End If
                If dr("Dept").ToString.Trim <> vo.Dept.Trim Then

                    cmd.Parameters.AddWithValue("?DeptChg", "Y")
                End If
                If dr("Location").ToString.Trim <> vo.Location.Trim Then

                    cmd.Parameters.AddWithValue("?LocationChg", "Y")
                End If

                If dr("ComputerName").ToString.Trim <> vo.ComputerName.Trim Then

                    cmd.Parameters.AddWithValue("?ComputerNameChg", "Y")
                End If

                If dr("Model").ToString.Trim <> vo.Model.Trim Then

                    cmd.Parameters.AddWithValue("?ModelChg", "Y")
                End If
                If dr("CPU").ToString.Trim <> vo.CPU.Trim Then

                    cmd.Parameters.AddWithValue("?CPUChg", "Y")
                End If


                If dr("FDD").ToString.Trim <> vo.FDD.Trim Then

                    cmd.Parameters.AddWithValue("?FDDChg", "Y")
                End If


                If dr("CD_R").ToString.Trim <> vo.CD_R.Trim Then

                    cmd.Parameters.AddWithValue("?CD_RChg", "Y")
                End If

                If dr("CD_RW").ToString.Trim <> vo.CD_RW.Trim Then

                    cmd.Parameters.AddWithValue("?CD_RWChg", "Y")
                End If
                If dr("MAC1").ToString.Trim <> vo.MAC1.Trim Then

                    cmd.Parameters.AddWithValue("?MAC1Chg", "Y")
                End If
                If dr("MAC2").ToString.Trim <> vo.MAC2.Trim Then

                    cmd.Parameters.AddWithValue("?MAC2Chg", "Y")
                End If
                If dr("PS").ToString.Trim <> vo.PS.Trim Then

                    cmd.Parameters.AddWithValue("?PSChg", "Y")
                End If

                If dr("NBLabel").ToString.Trim <> vo.NBLabel.Trim Then

                    cmd.Parameters.AddWithValue("?NBLabelChg", "Y")
                End If

                If dr("Remark").ToString.Trim <> vo.Remark.Trim Then

                    cmd.Parameters.AddWithValue("?RemarkChg", "Y")
                End If
                If dr("OS").ToString.Trim <> vo.OS.Trim Then

                    cmd.Parameters.AddWithValue("?OSChg", "Y")
                End If
                If dr("HDD").ToString.Trim <> vo.HDD.Trim Then

                    field1 += ",?HDDChg"
                    cmd.Parameters.AddWithValue("?HDDChg", "Y")
                End If
                If dr("RAM").ToString.Trim <> vo.RAM.Trim Then

                    cmd.Parameters.AddWithValue("?RAMChg", "Y")
                End If
                If dr("UsingType").ToString.Trim <> vo.UsingType.Trim Then

                    cmd.Parameters.AddWithValue("?UsingTypeChg", "Y")
                End If

                If dr("MonitorSize").ToString.Trim <> vo.MonitorSize.Trim Then

                    cmd.Parameters.AddWithValue("?MonitorSizeChg", "Y")
                End If
                If dr("Factory").ToString.Trim <> vo.Factory.Trim Then

                    cmd.Parameters.AddWithValue("?FactoryChg", "Y")
                End If

                If dr("storehouse").ToString.Trim <> vo.storehouse.Trim Then

                    cmd.Parameters.AddWithValue("?storehouseChg", "Y")
                End If

                If dr("StoreLocation").ToString.Trim <> vo.StoreLocation.Trim Then

                    cmd.Parameters.AddWithValue("?StoreLocationChg", "Y")
                End If

                If dr("UsingType2").ToString.Trim <> vo.UsingType2.Trim Then

                    cmd.Parameters.AddWithValue("?UsingType2Chg", "Y")
                End If

                If dr("HouseID").ToString.Trim <> vo.HouseID.Trim Then

                    cmd.Parameters.AddWithValue("?HouseIDChg", "Y")
                End If
                If dr("OtherRemark").ToString.Trim <> vo.OtherRemark.Trim Then

                    cmd.Parameters.AddWithValue("?OtherRemarkChg", "Y")
                End If

                If dr("Asset_Cost").ToString.Trim <> vo.Asset_Cost.Trim Then

                    cmd.Parameters.AddWithValue("?Asset_CostChg", "Y")
                End If

                If dr("UsingDesc").ToString.Trim <> vo.UsingDesc.Trim Then

                    cmd.Parameters.AddWithValue("?UsingDescChg", "Y")
                End If

                Dim logDao As AssetLogDAO = New AssetLogDAO(_dbName)
                Dim fromDept As String
                Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
                Try
                    fromDept = uDao.GetEmpData(dr("asset_owner").ToString).Rows(0)("Dept_ID").ToString
                Catch ex As Exception
                    fromDept = ""
                End Try

                'If doChgOwner Then
                If vo.asset_status = "Scrapped" Then ''作廢時

                    Dim dt As DataTable = logDao.GetFromDeptFromAssetOwner(dr("asset_serialNo").ToString)
                    If dr("asset_status") = "Storing" Then ''之前是繳回或作廢時,填入LOG的FromDept,否則填入資產的DEPT
                        If Not dt Is Nothing Then
                            cmd.Parameters.AddWithValue("?FromDept", dt.Rows(0)("FromDept").ToString.Trim)
                            cmd.Parameters.AddWithValue("?FromAsset_Owner", dt.Rows(0)("Fromasset_owner").ToString.Trim)
                        Else
                            cmd.Parameters.AddWithValue("?FromDept", fromDept)
                            cmd.Parameters.AddWithValue("?FromAsset_Owner", dr("asset_owner"))
                        End If
                    Else
                        cmd.Parameters.AddWithValue("?FromDept", fromDept)
                        cmd.Parameters.AddWithValue("?FromAsset_Owner", dr("asset_owner"))
                    End If
                Else
                    cmd.Parameters.AddWithValue("?FromDept", fromDept)
                    cmd.Parameters.AddWithValue("?FromAsset_Owner", dr("asset_owner"))

                End If



                'End If
                cmd.ExecuteNonQuery()
                con.Close()

            Catch ex As Exception
                con.Close()
                Throw ex
            Finally

            End Try
           
            If doChgOwner Then ''設備擁有者改變則需要送信給新擁有者,並紀錄一筆發信紀錄,將回報資料清空
                doChgOwnerSub(vo.ModiUID, vo.asset_serialNo, vo.asset_owner, "2", vo.assetdef_id, webPath)
             
            Else
                doNotChgOwnerSub(vo.ModiUID, vo.asset_serialNo, vo.asset_owner, ModiType, vo.assetdef_id, webPath)
            End If
        End Sub


        Public Sub doNotChgOwnerSub(ByVal UID As String, ByVal asset_serialNo As String, ByVal owner As String, ByVal ModiType As String, ByVal assetdef_id As String, ByVal webPath As String)
            ''設備擁有者改變則需要送信給新擁有者,並紀錄一筆發信紀錄,將回報資料清空
            '' InsertMailLog("6", asset_serialNo, UID) ''發信紀錄



            Dim dao1 As AssetManage.DAO.MailParamDAO = New AssetManage.DAO.MailParamDAO(_dbName)
            Dim dtPar As DataTable = dao1.GetMailParamByID("1") ''取得異動發信的MailBody
            Dim Language As String
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Language = uDao.GetLanguage(owner, _dbName)
            Dim Mailbody As String
            Dim modiName As String
            Dim def_name As String
            Dim assetTypeDt As DataTable = GetAssetType("0")

            Try
                def_name = assetTypeDt.Select("id=" + assetdef_id)(0)("name").ToString()
            Catch ex As Exception
                def_name = ""
            End Try
            Dim MailSubject As String


            Select Case Language
                Case "en-us"
                    Mailbody = dtPar.Rows(0)("MailBodyEng").ToString()
                    If ModiType = "0" Then
                        modiName = " has added  to you , "
                        MailSubject = "【OAMS notificatinon】" + asset_serialNo + " has added to you ！"
                    Else
                        modiName = " has transfered  to you , "
                        MailSubject = "【OAMS notificatinon】" + asset_serialNo + "  has modified ！"
                    End If
                Case "zh-cn"
                    Mailbody = dtPar.Rows(0)("MailBodyChn").ToString()
                    If ModiType = "0" Then
                        modiName = " 已新增 "

                        MailSubject = "【资产异动通知】" + asset_serialNo + "  已新增至您的名下！"
                    Else
                        modiName = " 已异动 "
                        MailSubject = "【资产异动通知】" + asset_serialNo + "  已异动！"
                    End If
                Case Else
                    Mailbody = dtPar.Rows(0)("MailBody").ToString()
                    If ModiType = "0" Then
                        modiName = " 已新增 "
                        MailSubject = "【資產異動通知】" + asset_serialNo + "  已新增至您的名下！"
                    Else
                        modiName = " 已異動 "
                        MailSubject = "【資產異動通知】" + asset_serialNo + "  已異動！"
                    End If
            End Select

            Dim ReturnDeadLine As DateTime
            Dim returnDays As Integer = Integer.Parse(dtPar.Rows(0)("ReturnDays").ToString)
            ReturnDeadLine = GetWorkDate(Today, returnDays, owner)


            Dim body As String


            Select Case Language
                Case "en-us"
                    body = "OA asset Catory :" + def_name + "  OA Asset serial no :" + asset_serialNo + modiName + " you can link <a href='" + webPath + "'> OAAsset Manage System </a> Confirm it ，"
                Case "zh-cn"
                    body = "OA资产类别:" + def_name + "  OA资产编号:" + asset_serialNo + modiName + "，您可进入<a href='" + webPath + "'> OA资产管理系统</a> 查询确认，"
                Case Else
                    body = "OA資產類別:" + def_name + "  OA資產編號:" + asset_serialNo + modiName + "，您可進入<a href='" + webPath + "'> OA資產管理系統</a> 查詢確認，"
            End Select




            If Mailbody.IndexOf("|$|資料|$|") = -1 Then

                Mailbody += "<br/>" + body
            Else
                Mailbody = Mailbody.Replace("|$|資料|$|", body)
            End If

            Dim cc As String = ""   '代理人
            Try
                cc = uDao.GetVIPAgent(owner)("EMail").ToString.Trim()
            Catch ex As Exception

            End Try
            Dim mail As New MailMessage()
            mail.Subject = MailSubject
            mail.Body = Mailbody

            Dim MailType As String = System.Configuration.ConfigurationManager.AppSettings("MailType")
            Try
                Dim mailFrom As String = System.Configuration.ConfigurationManager.AppSettings("MailFrom")
                Dim MailTo As String = uDao.GetUsers(owner).Rows(0)("email").ToString()
                Dim SMTP As String = System.Configuration.ConfigurationManager.AppSettings("SMTPServer")
                mail.From = New System.Net.Mail.MailAddress(mailFrom, "OA系統通知")

                mail.IsBodyHtml = True
                If MailType.ToLower = "online" Then '正式才要cc
                    If cc <> "" Then
                        cc = uDao.GetUsers(cc).Rows(0)("EMail").ToString
                        mail.CC.Add(New MailAddress(cc))
                        mail.Subject = mail.Subject + ",這是代理人信件,請不要任意刪除!"
                    Else
                        mail.To.Add(New MailAddress(MailTo))
                    End If
                Else
                    mail.To.Add(New MailAddress(MailTo))
                End If

                Dim smtpServer As SmtpClient = New SmtpClient(SMTP)
                smtpServer.Send(mail)
            Catch ex As Exception

            End Try

           

            ' ClearReplyWithNoLog(asset_serialNo, UID, ReturnDeadLine, Integer.Parse(dtPar.Rows(0)("ResendDays")), Integer.Parse(dtPar.Rows(0)("ResendTimes")), MailSubject, Mailbody, returnDays)
        End Sub


        Public Sub doChgOwnerSub(ByVal UID As String, ByVal asset_serialNo As String, ByVal owner As String, ByVal ModiType As String, ByVal assetdef_id As String, ByVal webPath As String)
            ''設備擁有者改變則需要送信給新擁有者,並紀錄一筆發信紀錄,將回報資料清空
            '' InsertMailLog("6", asset_serialNo, UID) ''發信紀錄



            Dim dao1 As AssetManage.DAO.MailParamDAO = New AssetManage.DAO.MailParamDAO(_dbName)
            Dim dtPar As DataTable = dao1.GetMailParamByID("1") ''取得異動發信的MailBody
            Dim Language As String
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Language = uDao.GetLanguage(owner, _dbName)
            Dim Mailbody As String
            Dim modiName As String
            Dim def_name As String
            Dim assetTypeDt As DataTable = GetAssetType("0")

            Try
                def_name = assetTypeDt.Select("id=" + assetdef_id)(0)("name").ToString()
            Catch ex As Exception
                def_name = ""
            End Try
            Dim MailSubject As String


            Select Case Language
                Case "en-us"
                    Mailbody = dtPar.Rows(0)("MailBodyEng").ToString()
                    If ModiType = "0" Then
                        modiName = " has added  to you , "
                        MailSubject = "【OAMS notificatinon】" + asset_serialNo + " has added to you ！"
                    Else
                        modiName = " has transfered  to you , "
                        MailSubject = "【OAMS notificatinon】" + asset_serialNo + "  has transfered  to you！"
                    End If
                Case "zh-cn"
                    Mailbody = dtPar.Rows(0)("MailBodyChn").ToString()
                    If ModiType = "0" Then
                        modiName = " 已新增 "

                        MailSubject = "【资产异动通知】" + asset_serialNo + "  已新增至您的名下！"
                    Else
                        modiName = " 已移转 "
                        MailSubject = "【资产移转通知】" + asset_serialNo + "  已移转至您的名下！"
                    End If
                Case Else
                    Mailbody = dtPar.Rows(0)("MailBody").ToString()
                    If ModiType = "0" Then
                        modiName = " 已新增 "
                        MailSubject = "【資產異動通知】" + asset_serialNo + "  已新增至您的名下！"
                    Else
                        modiName = " 已移轉 "
                        MailSubject = "【資產移轉通知】" + asset_serialNo + "  已移轉至您的名下！"
                    End If
            End Select

            Dim ReturnDeadLine As DateTime
            Dim returnDays As Integer = Integer.Parse(dtPar.Rows(0)("ReturnDays").ToString)
            ReturnDeadLine = GetWorkDate(Today, returnDays, owner)


            Dim body As String
           

            Select Case Language
                Case "en-us"
                    body = "OA asset Catory :" + def_name + "  OA Asset serial no :" + asset_serialNo + modiName + " you can link <a href='" + webPath + "'> OAAsset Manage System </a> Confirm it ，"
                Case "zh-cn"
                    body = "OA资产类别:" + def_name + "  OA资产编号:" + asset_serialNo + modiName + "至您的名下，您可进入<a href='" + webPath + "'> OA资产管理系统</a> 查询确认，"
                Case Else
                    body = "OA資產類別:" + def_name + "  OA資產編號:" + asset_serialNo + modiName + "至您的名下，您可進入<a href='" + webPath + "'> OA資產管理系統</a> 查詢確認，"
            End Select




            If Mailbody.IndexOf("|$|資料|$|") = -1 Then

                Mailbody += "<br/>" + body
            Else
                Mailbody = Mailbody.Replace("|$|資料|$|", body)
            End If

            Dim cc As String = ""   '代理人
            Try
                cc = uDao.GetVIPAgent(owner)
            Catch ex As Exception

            End Try
            Dim mail As New MailMessage()
            mail.Subject = MailSubject
            mail.Body = Mailbody
            Try
                Dim MailType As String = System.Configuration.ConfigurationManager.AppSettings("MailType")
                Dim mailFrom As String = System.Configuration.ConfigurationManager.AppSettings("MailFrom")
                Dim MailTo As String = ""
                If MailType.ToLower <> "online" Then '測試
                    MailTo = System.Configuration.ConfigurationManager.AppSettings("MailTo")
                Else
                    MailTo = uDao.GetUsers(owner).Rows(0)("email").ToString()

                End If


                Dim SMTP As String = System.Configuration.ConfigurationManager.AppSettings("SMTPServer")
                mail.From = New System.Net.Mail.MailAddress(mailFrom, "OA系統通知")
                '   mail.To.Add(New MailAddress(MailTo))
                mail.IsBodyHtml = True
                If MailType.ToLower = "online" Then '正式才要cc

                    If cc <> "" Then
                        cc = uDao.GetUsers(cc).Rows(0)("EMail").ToString
                        mail.CC.Add(New MailAddress(cc))
                        mail.Subject = mail.Subject + ",這是代理人信件,請不要任意刪除!"
                    Else
                        mail.To.Add(New MailAddress(MailTo))
                    End If
                Else
                    mail.To.Add(New MailAddress(MailTo))
                End If
                Dim smtpServer As SmtpClient = New SmtpClient(SMTP)
                smtpServer.Send(mail)
            Catch ex As Exception

            End Try


            '  ClearReplyWithNoLog(asset_serialNo, UID, ReturnDeadLine, Integer.Parse(dtPar.Rows(0)("ResendDays")), Integer.Parse(dtPar.Rows(0)("ResendTimes")), MailSubject, Mailbody, returnDays)
        End Sub



        Public Sub InsertAssetLog(ByVal UsingType2 As String, ByVal StoreHouse As String, ByVal StoreLocation As String, ByVal User1 As String, ByVal UID As String, ByVal asset_serialNo As String, ByVal UsingDesc As String)
            'ModiType -->   0:.新增1.異動2.移轉3.回報4.繳回5.取消回報6.寄信7.除帳
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            Dim HTDao As AssetManage.DAO.HouseTypeDAO = New AssetManage.DAO.HouseTypeDAO(_dbName)
            Dim houseType As String
            Try
                houseType = HTDao.GetHouseType(StoreHouse, "").Rows(0)("House_Type").ToString()
            Catch ex As Exception
                houseType = "1"   '找不到則定為個人
            End Try

            sb.AppendLine("select * from  V_AST_ASSETALL  where asset_serialNo=@asset_serialNo ")
            '===========================
            sqlParams(0) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, asset_serialNo.Length)
            sqlParams(0).Value = asset_serialNo

            Dim dr As DataRow = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows(0)
            ''新增到mySql中

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim sb1 As New StringBuilder()


            Try
                sb1.AppendLine(" select max(SeqNo)+1 as MAX1 from v_ast_assetalllog where asset_serialNo=?asset_serialNo ")

                Dim oTable As Data.DataTable

                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", asset_serialNo)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 1
                End If
                con.Open()
                sb1.Length = 0
                Dim field As String = "asset_serialNo, ModiType, SeqNo,ModiUID,ModiDate"
                Dim field1 As String = "?asset_serialNo,?ModiType,?SeqNo,?ModiUID,?ModiDate"

                'If dr("User1").ToString.Trim <> User1.Trim() Then
                field += ",User1"
                field1 += ",?User1"
                'End If
                'If dr("StoreHouse").ToString.Trim <> StoreHouse.Trim() Then
                field += ",StoreHouse"
                field1 += ",?StoreHouse"
                'End If
                'If dr("StoreLocation").ToString.Trim <> StoreLocation.Trim() Then
                field += ",StoreLocation"
                field1 += ",?StoreLocation"
                'End If
                'If dr("UsingType2").ToString.Trim <> UsingType2.Trim() Then
                field += ",UsingType2"
                field1 += ",?UsingType2"
                'End If

                'If dr("UsingDesc").ToString.Trim <> UsingDesc.Trim() Then
                field += ",UsingDesc"
                field1 += ",?UsingDesc"
                field += ",HouseID"
                field1 += ",?HouseID"
                'End If
                sb1.AppendLine(" insert into v_ast_assetalllog (" + field + ") values (" + field1 + ");  ")

                sb1.AppendLine("Update v_ast_assetalllog  set HouseID=?HouseID  where asset_serialNo=?asset_serialNo")
                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", asset_serialNo)
                cmd.Parameters.AddWithValue("?ModiType", "1")
                cmd.Parameters.AddWithValue("?SeqNo", max)
                cmd.Parameters.AddWithValue("?ModiUID", UID)
                cmd.Parameters.AddWithValue("?ModiDate", DateTime.Now)

                'If dr("User1").ToString.Trim <> User1.Trim() Then

                cmd.Parameters.AddWithValue("?User1", User1)
                'End If
                'If dr("StoreHouse").ToString.Trim <> StoreHouse.Trim() Then
                cmd.Parameters.AddWithValue("?StoreHouse", StoreHouse)
                'End If
                'If dr("StoreLocation").ToString.Trim <> StoreLocation.Trim() Then
                cmd.Parameters.AddWithValue("?StoreLocation", StoreLocation)

                'End If
                'If dr("UsingType2").ToString.Trim <> UsingType2.Trim() Then
                cmd.Parameters.AddWithValue("?UsingType2", UsingType2)
                'End If



                'If dr("UsingDesc").ToString.Trim <> UsingDesc.Trim() Then
                cmd.Parameters.AddWithValue("?UsingDesc", UsingDesc)
                cmd.Parameters.AddWithValue("?HouseID", houseType)
                'End If




                'If dr("User2").ToString.Trim <> User2.Trim() Then
                '    cmd.Parameters.AddWithValue("?User2Chg", "Y")
                'Else
                '    cmd.Parameters.AddWithValue("?User2Chg", "")
                'End If
                'If dr("StoreHouse").ToString.Trim <> StoreHouse.Trim() Then
                '    cmd.Parameters.AddWithValue("?StoreHouseChg", "Y")
                'Else
                '    cmd.Parameters.AddWithValue("?StoreHouseChg", "")
                'End If
                'If dr("StoreLocation").ToString.Trim <> StoreLocation.Trim() Then
                '    cmd.Parameters.AddWithValue("?StoreLocationChg", "Y")
                'Else
                '    cmd.Parameters.AddWithValue("?StoreLocationChg", "")
                'End If
                'If dr("UsingType2").ToString.Trim <> UsingType2.Trim() Then
                '    cmd.Parameters.AddWithValue("?UsingType2Chg", "Y")
                'Else
                '    cmd.Parameters.AddWithValue("?UsingType2Chg", "")
                'End If
                cmd.ExecuteNonQuery()

            Catch ex As Exception
            Finally
                con.Close()

            End Try


        End Sub

        Public Sub ClearReplyWithNoLog(ByVal asset_serialNo As String, ByVal byWho As String, ByVal ReturnDeadLine As DateTime, ByVal ResendDays As Integer, ByVal ResendTimes As Integer, ByVal MailSubject As String, ByVal MailBody As String, ByVal ReturnDays As Integer)

            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(7) {}

            sb.AppendLine("update V_AST_ASSETALL set AppID=isnull(AppID,0)+1,ReturnDate=Null,ReturnBy='', ReturnDeadLine=@ReturnDeadLine , ReturnDeadLineNow=@ReturnDeadLine  ")
            sb.AppendLine(",ResendDays=@ResendDays,ResendTimes=@ResendTimes,ResendTimesNow=0,ResendDate=@ResendDate,MailSubject=@MailSubject,MailBody=@MailBody ")

            sb.AppendLine("where asset_serialNo=@asset_serialNo  ")


            sqlParams(0) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, asset_serialNo.Length)
            sqlParams(0).Value = asset_serialNo
            sqlParams(1) = New SqlParameter("@ReturnDeadLine", SqlDbType.DateTime)
            sqlParams(1).Value = ReturnDeadLine
            sqlParams(2) = New SqlParameter("@ResendDays", SqlDbType.Int)
            sqlParams(2).Value = ResendDays
            sqlParams(3) = New SqlParameter("@ResendTimes", SqlDbType.Int)
            sqlParams(3).Value = ResendTimes
            sqlParams(4) = New SqlParameter("@ResendDate", SqlDbType.DateTime)
            sqlParams(4).Value = GetWorkDate(ReturnDeadLine, ReturnDays, byWho)
            sqlParams(5) = New SqlParameter("@MailSubject", SqlDbType.NVarChar, MailSubject.Length)
            sqlParams(5).Value = MailSubject
            sqlParams(6) = New SqlParameter("@MailBody", SqlDbType.NVarChar, MailBody.Length)
            sqlParams(6).Value = MailBody
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
          

        End Sub


        ''' <summary>
        ''' (取消)回報修改資料及新增1筆(取消)回報log
        ''' </summary>
        ''' <param name="asset_serialNo">資產編號</param>
        ''' <param name="byWho">執行人</param>
        ''' <param name="CancelReason">取消原因選項</param>
        ''' <param name="CancelRemark">取消說明</param>
        '''  <param name="ModiType">異動種類: 0:.新增1.異動2.移轉3.回報4.繳回5.取消回報6.寄信</param>
        ''' <remarks> (取消)回報修改資料及新增1筆(取消)回報log</remarks>
        Public Sub UpdateReply(ByVal asset_serialNo As String, ByVal byWho As String, ByVal CancelReason As String, ByVal CancelRemark As String, ByVal ModiType As String)
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(3) {}

            sb.AppendLine("update  V_AST_ASSETALL set AppID=isnull(AppID,0)+1, ReturnDate=@ReturnDate,ReturnBy=@ReturnBy  where asset_serialNo=@asset_serialNo  ")


            sqlParams(0) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar, asset_serialNo.Length)
            sqlParams(0).Value = asset_serialNo
            sqlParams(1) = New SqlParameter("@ReturnBy", SqlDbType.NVarChar, byWho.Length)
            sqlParams(2) = New SqlParameter("@ReturnDate", SqlDbType.DateTime)
            If ModiType = "3" Then
                sqlParams(1).Value = byWho
                sqlParams(2).Value = Now
            Else
                sqlParams(1).Value = ""
                sqlParams(2).Value = DBNull.Value
            End If

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim sb1 As New StringBuilder()
            Dim dr As DataRow



            Try
                dr = GetAssetData("", "", asset_serialNo, "", "", "", "", "", "").Rows(0)
                sb1.AppendLine(" select max(SeqNo)+1 as MAX1 from v_ast_assetalllog where asset_serialNo=?asset_serialNo ")

                Dim oTable As Data.DataTable

                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", asset_serialNo)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 1
                End If
                con.Open()
                sb1.Length = 0

                sb1.AppendLine("insert into v_ast_assetalllog (asset_serialNo,SeqNo,ModiType,ReturnBy,ReturnDate,CancelReason,CancelRemark,ModiUID,ModiDate,asset_Owner,Dept,MAC1,MAC2,ComputerName,HouseID,assetDef_id) values (?asset_serialNo,?SeqNo,?ModiType,?ReturnBy,?ReturnDate,?CancelReason,?CancelRemark,?ModiUID,?ModiDate,?asset_Owner,?Dept,?MAC1,?MAC2,?ComputerName,?HouseID,?assetDef_id)")
                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", asset_serialNo)
                cmd.Parameters.AddWithValue("?SeqNo", max)
                cmd.Parameters.AddWithValue("?ModiType", ModiType)
                cmd.Parameters.AddWithValue("?ReturnBy", byWho)
                cmd.Parameters.AddWithValue("?ReturnDate", DateTime.Now)
                cmd.Parameters.AddWithValue("?CancelReason", CancelReason)
                cmd.Parameters.AddWithValue("?CancelRemark", CancelRemark)
                cmd.Parameters.AddWithValue("?ModiUID", byWho)
                cmd.Parameters.AddWithValue("?ModiDate", Now)
                cmd.Parameters.AddWithValue("?asset_Owner", dr("asset_Owner").ToString)
                cmd.Parameters.AddWithValue("?Dept", dr("Dept").ToString)
                cmd.Parameters.AddWithValue("?MAC1", dr("MAC1").ToString)
                cmd.Parameters.AddWithValue("?MAC2", dr("MAC2").ToString)
                cmd.Parameters.AddWithValue("?ComputerName", dr("ComputerName").ToString)
                cmd.Parameters.AddWithValue("?HouseID", dr("HouseID").ToString)
                cmd.Parameters.AddWithValue("?assetDef_id", dr("assetDef_id").ToString)
                cmd.ExecuteNonQuery()
                con.Close()
            Catch ex As Exception
                ErrorLog("AssetDAO", ex.Message, "UpdateReply", "", "0", _dbName)
                con.Close()
                Throw ex
            Finally


            End Try








        End Sub


        Public Sub InsertForm87DataHead(ByVal FORM_NO As Integer, ByVal FORM_APPLICANT As String, ByVal APPLICANT_DEPT As String, ByVal APPLICANT_EXT As String, ByVal PLACE_LATER As String, ByVal MOVE_CAUSE As String)
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(6) {}


            sb.AppendLine("insert into AUOFORM87 (FORM_NO, FORM_APPLICANT, APPLICANT_DEPT, APPLICANT_EXT, PLACE_LATER, MOVE_CAUSE) values (@FORM_NO, @FORM_APPLICANT, @APPLICANT_DEPT, @APPLICANT_EXT, @PLACE_LATER, @MOVE_CAUSE) ")
            '===========================.



            sqlParams(0) = New SqlParameter("@FORM_NO", SqlDbType.Int)
            sqlParams(0).Value = FORM_NO
            sqlParams(1) = New SqlParameter("@FORM_APPLICANT", SqlDbType.NVarChar, FORM_APPLICANT.Length)
            sqlParams(1).Value = FORM_APPLICANT
            sqlParams(2) = New SqlParameter("@APPLICANT_DEPT", SqlDbType.NVarChar, APPLICANT_DEPT.Length)
            sqlParams(2).Value = APPLICANT_DEPT
            sqlParams(3) = New SqlParameter("@APPLICANT_EXT", SqlDbType.NVarChar, APPLICANT_EXT.Length)
            sqlParams(3).Value = APPLICANT_EXT
            sqlParams(4) = New SqlParameter("@PLACE_LATER", SqlDbType.NVarChar, PLACE_LATER.Length)
            sqlParams(4).Value = PLACE_LATER
            sqlParams(5) = New SqlParameter("@MOVE_CAUSE", SqlDbType.NVarChar, MOVE_CAUSE.Length)
            sqlParams(5).Value = MOVE_CAUSE
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

        End Sub

        Public Sub InsertForm87DataDetail(ByVal ModiType As String, ByVal FORM_NO As Integer, ByVal ASSET_NO As String, ByVal ASSET_OWNER_EMP_NO_BEFORE As String, ByVal ASSET_OWNER_EMP_NO_LATER As String, ByVal ASSET_USER_EMP_NO_LATER As String, ByVal ASSET_USER_INFO_LATER As String, ByVal byWho As String, ByVal SITE_APPROVER1 As String, ByVal SITE_APPROVER2 As String, ByVal SITE_APPROVER3 As String, ByVal SITE_APPROVEROA As String)

            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(20) {}
            Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Dim aDao As AssetManage.DAO.AssetDAO = New AssetManage.DAO.AssetDAO(_dbName)
            Dim dr1, dr2 As DataRow



            sb.AppendLine(" insert into  AUOFORM87_ASSET_SELECTED ([FORM_NO],[ASSET_NO],[ASSET_NAME],[ASSET_OWNER_EMP_NO_BEFORE],[ASSET_OWNER_NAME_BEFORE],[ASSET_OWNER_DEPT_BEFORE],[ASSET_OWNER_EXT_BEFORE],[ASSET_OWNER_EMP_NO_LATER],[ASSET_OWNER_NAME_LATER],[ASSET_OWNER_DEPT_LATER],[ASSET_OWNER_EXT_LATER],[ASSET_OWNER_SITE_NAME_LATER],[ASSET_USER_EMP_NO_LATER],[ASSET_USER_INFO_LATER],[ASSET_USER_DESC_LATER] ,[SITE_APPROVER1] ,[SITE_APPROVER2],[SITE_APPROVER3],[SITE_APPROVEROA],[ASSET_STATUS],[SITE_APPROVERSCRAP]) values")
            sb.AppendLine(" (   @FORM_NO ,@ASSET_NO,@ASSET_NAME,@ASSET_OWNER_EMP_NO_BEFORE,@ASSET_OWNER_NAME_BEFORE,@ASSET_OWNER_DEPT_BEFORE,@ASSET_OWNER_EXT_BEFORE,@ASSET_OWNER_EMP_NO_LATER,@ASSET_OWNER_NAME_LATER,@ASSET_OWNER_DEPT_LATER,@ASSET_OWNER_EXT_LATER    ,@ASSET_OWNER_SITE_NAME_LATER ,@ASSET_USER_EMP_NO_LATER,@ASSET_USER_INFO_LATER,@ASSET_USER_DESC_LATER,@SITE_APPROVER1,@SITE_APPROVER2,@SITE_APPROVER3,@SITE_APPROVEROA,@ASSET_STATUS,@SITE_APPROVERSCRAP) ")
            '===========================.w 
            sqlParams(0) = New SqlParameter("@FORM_NO", SqlDbType.Int)
            sqlParams(0).Value = FORM_NO
            sqlParams(1) = New SqlParameter("@ASSET_NO", SqlDbType.NVarChar)
            sqlParams(1).Value = ASSET_NO
            dr2 = aDao.GetAssetData("", "", ASSET_NO, "", "", "", "", "", "").Rows(0)
            sqlParams(2) = New SqlParameter("@ASSET_NAME", SqlDbType.NVarChar)
            sqlParams(2).Value = dr2("DefName").ToString
            sqlParams(3) = New SqlParameter("@ASSET_OWNER_EMP_NO_BEFORE", SqlDbType.NVarChar)
            sqlParams(3).Value = ASSET_OWNER_EMP_NO_BEFORE
            Try
                dr1 = uDao.GetEmpData(ASSET_OWNER_EMP_NO_BEFORE).Rows(0)


                sqlParams(4) = New SqlParameter("@ASSET_OWNER_NAME_BEFORE", SqlDbType.NVarChar)
                sqlParams(4).Value = dr1("UserName").ToString
                sqlParams(5) = New SqlParameter("@ASSET_OWNER_DEPT_BEFORE", SqlDbType.NVarChar)
                sqlParams(5).Value = dr1("Dept_ID").ToString
                sqlParams(6) = New SqlParameter("@ASSET_OWNER_EXT_BEFORE", SqlDbType.NVarChar)
                sqlParams(6).Value = dr1("Ext_no").ToString

            Catch ex As Exception


                sqlParams(4) = New SqlParameter("@ASSET_OWNER_NAME_BEFORE", SqlDbType.NVarChar)
                sqlParams(4).Value = ""
                sqlParams(5) = New SqlParameter("@ASSET_OWNER_DEPT_BEFORE", SqlDbType.NVarChar)
                sqlParams(5).Value = "'"
                sqlParams(6) = New SqlParameter("@ASSET_OWNER_EXT_BEFORE", SqlDbType.NVarChar)
                sqlParams(6).Value = ""

            End Try
            sqlParams(7) = New SqlParameter("@ASSET_OWNER_EMP_NO_LATER", SqlDbType.NVarChar)
            sqlParams(7).Value = ASSET_OWNER_EMP_NO_LATER
            Try
                dr1 = uDao.GetEmpData(ASSET_OWNER_EMP_NO_LATER).Rows(0)
                sqlParams(8) = New SqlParameter("@ASSET_OWNER_NAME_LATER", SqlDbType.NVarChar)
                sqlParams(8).Value = dr1("UserName").ToString
                sqlParams(9) = New SqlParameter("@ASSET_OWNER_DEPT_LATER", SqlDbType.NVarChar)
                sqlParams(9).Value = dr1("Dept_ID").ToString
                sqlParams(10) = New SqlParameter("@ASSET_OWNER_EXT_LATER", SqlDbType.NVarChar)
                sqlParams(10).Value = dr1("Ext_No").ToString
                sqlParams(11) = New SqlParameter("@ASSET_OWNER_SITE_NAME_LATER", SqlDbType.NVarChar)
                sqlParams(11).Value = dr1("Factory_ID").ToString

            Catch ex As Exception
                sqlParams(8) = New SqlParameter("@ASSET_OWNER_NAME_LATER", SqlDbType.NVarChar)
                sqlParams(8).Value = ""
                sqlParams(9) = New SqlParameter("@ASSET_OWNER_DEPT_LATER", SqlDbType.NVarChar)
                sqlParams(9).Value = ""
                sqlParams(10) = New SqlParameter("@ASSET_OWNER_EXT_LATER", SqlDbType.NVarChar)
                sqlParams(10).Value = ""
                sqlParams(11) = New SqlParameter("@ASSET_OWNER_SITE_NAME_LATER", SqlDbType.NVarChar)
                sqlParams(11).Value = ""
            End Try
            sqlParams(12) = New SqlParameter("@ASSET_USER_EMP_NO_LATER", SqlDbType.NVarChar)
            sqlParams(12).Value = ASSET_USER_EMP_NO_LATER
            Try
                dr1 = uDao.GetEmpData(ASSET_USER_EMP_NO_LATER).Rows(0)
                sqlParams(13) = New SqlParameter("@ASSET_USER_INFO_LATER", SqlDbType.NVarChar)
                sqlParams(13).Value = dr1("Dept_ID").ToString + "/" + dr1("UserName").ToString
            Catch ex As Exception
                sqlParams(13) = New SqlParameter("@ASSET_USER_INFO_LATER", SqlDbType.NVarChar)
                sqlParams(13).Value = ""
            End Try

            sqlParams(14) = New SqlParameter("@ASSET_USER_DESC_LATER", SqlDbType.NVarChar)
            sqlParams(14).Value = ""
            sqlParams(15) = New SqlParameter("@SITE_APPROVER1", SqlDbType.NVarChar)
            sqlParams(15).Value = SITE_APPROVER1
            sqlParams(16) = New SqlParameter("@SITE_APPROVER2", SqlDbType.NVarChar)
            sqlParams(16).Value = SITE_APPROVER2
            sqlParams(17) = New SqlParameter("@SITE_APPROVER3", SqlDbType.NVarChar)
            sqlParams(17).Value = SITE_APPROVER3
            sqlParams(18) = New SqlParameter("@SITE_APPROVEROA", SqlDbType.NVarChar)
            sqlParams(18).Value = SITE_APPROVEROA
            sqlParams(19) = New SqlParameter("@ASSET_STATUS", SqlDbType.NVarChar)
            If ModiType = "1" Then
                sqlParams(19).Value = "Distributed"
            ElseIf ModiType = "2" Then

                sqlParams(19).Value = "Storing"
            Else
                sqlParams(19).Value = "Scrapping"
            End If

            sqlParams(20) = New SqlParameter("@SITE_APPROVERSCRAP", SqlDbType.NVarChar)
            sqlParams(20).Value = ""






            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            If ModiType = "1" Then
                InsertAssetApplyLog(byWho, ASSET_NO, "2", FORM_NO)

            ElseIf ModiType = "2" Then

                InsertAssetApplyLog(byWho, ASSET_NO, "4", FORM_NO)
            Else
                InsertAssetApplyLog(byWho, ASSET_NO, "7", FORM_NO)
            End If



        End Sub



        Public Sub InsertAssetApplyLog(ByVal UID As String, ByVal asset_serialNo As String, ByVal ModiType As String, ByVal FormNo As Integer)

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim sb1 As New StringBuilder()


            Try
                sb1.AppendLine(" select max(SeqNo)+1 as MAX1 from v_ast_assetalllog where asset_serialNo=?asset_serialNo ")

                Dim oTable As Data.DataTable

                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", asset_serialNo)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 1
                End If
                con.Open()
                sb1.Length = 0
                Dim field As String = "asset_serialNo, ModiType, SeqNo,ModiUID,ModiDate"
                Dim field1 As String = "?asset_serialNo,?ModiType,?SeqNo,?ModiUID,?ModiDate"


                sb1.AppendLine(" insert into v_ast_assetalllog (" + field + ") values (" + field1 + ")  ")


                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", asset_serialNo)
                cmd.Parameters.AddWithValue("?ModiType", ModiType)
                cmd.Parameters.AddWithValue("?SeqNo", max)
                cmd.Parameters.AddWithValue("?ModiUID", UID)
                cmd.Parameters.AddWithValue("?ModiDate", DateTime.Now)

                '2011/8/9 remark by andy ''會多出1筆繳回的記錄.
                '  cmd.ExecuteNonQuery()

            Catch ex As Exception
            Finally
                con.Close()

            End Try
            sb1.Length = 0
            sb1.AppendLine("update V_AST_ASSETALL set  Apply='1' ,ApplyFormNo=" + FormNo.ToString() + "   where asset_serialNo='" + asset_serialNo + "'")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb1.ToString()).ToString()

        End Sub

        Public Function GetAssetCost(ByVal FactoryID As String, ByVal Model As String) As String

            Dim sb As New StringBuilder()
            sb.AppendLine(" Select * from oa_assetcost  where FactoryID=?FactoryID and Model =?Model")
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                cmd = New MySqlCommand(sb.ToString(), con)

                cmd.Parameters.AddWithValue("?FactoryID", FactoryID)
                cmd.Parameters.AddWithValue("?Model", Model)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                con.Close()

                If oTable.Rows.Count = 0 Then
                    Return ""
                Else
                    Return oTable.Rows(0)("Price").ToString
                End If


            Catch ex As Exception
                con.Close()
                Throw ex

            Finally


            End Try
         
        End Function
        Public Function GetAssetTypeName(ByVal def_id As String) As String
            Dim sb As New StringBuilder()

            sb.AppendLine(" select rtrim(assetdef_nameCN) as name  from C_AST_ASSETDEF  where def_id=" + def_id)
            Try
                Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString()).ToString
            Catch ex As Exception
                Return ""
            End Try


        End Function


        Public Function GetUnReplyData(ByVal def_id As String, ByVal Owner As String, ByVal asset_serialNo As String, ByVal Dept As String, ByVal HouseID As String, ByVal ReturnDeadLine1 As DateTime, ByVal ReturnDeadLine2 As DateTime, ByVal SendType As String) As DataTable
            Dim sb As New StringBuilder()
            If def_id = "" Then
                def_id = "0"
            End If
            If SendType = "" Then
                SendType = "0"
            End If
            Dim sqlParams As SqlParameter() = New SqlParameter(7) {}
            sb.AppendLine(" select C.assetdef_nameEN as DefName,A.*,B.emp_name as OwnerName  from V_AST_ASSETALL  A  left join AUOREF.dbo.emp_data_all B On A.asset_owner=B.emp_no  left join C_AST_ASSETDEF  C on A.assetdef_id=C.def_id  where (@def_id=0  or A.assetdef_id=@def_id) and (@Owner='' or A.asset_owner=@Owner ) and (@Dept='' or B.org_id like '%'+@Dept+'%')  and (A.HouseID=@HouseID or @HouseID='0')  and A.ReturnDeadLine>=@ReturnDeadLine1  and A.ReturnDeadLine<=@ReturnDeadLine2")
            sb.AppendLine(" and ( A.ParamID=@ParamID or @ParamID=0) and isNull(A.ReturnBy,'')=''  and (@asset_serialNo='' or A.asset_serialNo=@asset_serialNo)")

            sqlParams(0) = New SqlParameter("@def_id", SqlDbType.NVarChar)
            sqlParams(0).Value = def_id
            sqlParams(1) = New SqlParameter("@Owner", SqlDbType.NVarChar)
            sqlParams(1).Value = Owner
            sqlParams(2) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar)
            sqlParams(2).Value = asset_serialNo
            sqlParams(3) = New SqlParameter("@Dept", SqlDbType.NVarChar)
            sqlParams(3).Value = Dept
            sqlParams(4) = New SqlParameter("@HouseID", SqlDbType.NVarChar)
            sqlParams(4).Value = HouseID
            sqlParams(5) = New SqlParameter("@ReturnDeadLine1", SqlDbType.DateTime)
            sqlParams(5).Value = ReturnDeadLine1
            sqlParams(6) = New SqlParameter("@ReturnDeadLine2", SqlDbType.DateTime)
            sqlParams(6).Value = ReturnDeadLine2
            sqlParams(7) = New SqlParameter("@ParamID", SqlDbType.Int)
            sqlParams(7).Value = SendType
            'sqlParams(8) = New SqlParameter("@asset_serialNo", SqlDbType.NVarChar)
            'sqlParams(8).Value = asset_serialNo

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)

        End Function

        Public Function GetReplyStatistics(ByVal Kind As String, ByVal assetdef_id As String, ByVal dept As String, ByVal ReturnDeadLine1 As DateTime, ByVal ReturnDeadLine2 As DateTime, ByVal HouseID As String) As DataTable
            Dim udao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
            Try

           
                'add by Tim Yang at 2011/07/22
                Dim dicAssetOwner As New Dictionary(Of String, Boolean)
                Dim dicAssetId As New Dictionary(Of String, Boolean)
                Dim dicDept As New Dictionary(Of String, Boolean)

                Dim sbAssetOwner As New StringBuilder(256)
                Dim sbAssetId As New StringBuilder(256)
                Dim sbDept As New StringBuilder(256)


                If Kind = "2" Then

                    Dim sb As New StringBuilder()
                    Dim sqlParams As SqlParameter() = New SqlParameter(4) {}
                    'sb.AppendLine("  select asset_owner ,assetdef_id ,COUNT(asset_serialNo )  as TtlQty ")
                    'sb.AppendLine(" into #AA ")
                    'sb.AppendLine("  from V_AST_ASSETALL  ")
                    'sb.AppendLine(" where not ReturnDeadLine is null  and asset_status='Distributed' ")
                    'sb.AppendLine(" and SendDate>=@ReturnDeadLine1 and SendDate<=@ReturnDeadLine2")
                    'sb.AppendLine(" group by asset_owner ,assetdef_id  ")
                    'sb.AppendLine(" order by asset_owner ,assetdef_id  ")
                    'sb.AppendLine(" select asset_owner ,assetdef_id ,COUNT(asset_serialNo ) as ReturnQty,sum(datediff(day,  SendDate,ReturnDate))  as TtlReturnDays  ")
                    'sb.AppendLine(" into #BB ")
                    'sb.AppendLine("  from V_AST_ASSETALL  ")

                    'sb.AppendLine(" where  asset_status='Distributed' and ReturnBy <>'' ")
                    'sb.AppendLine(" and SendDate>=@ReturnDeadLine1 and SendDate<=@ReturnDeadLine2")

                    'sb.AppendLine(" group by asset_owner ,assetdef_id ,SendDate ")
                    'sb.AppendLine(" order by asset_owner ,assetdef_id  ")

                    'sb.AppendLine(" select A.asset_owner, A.assetdef_id,C.emp_name , D.assetdef_nameEN as def_Name,A.TtlQty*1.0 as TtlQty, B.ReturnQty *1.0 as ReturnQty")
                    'sb.AppendLine(" ,round(((100.0*(ISNULL(B.ReturnQty ,0.0)))/A.TtlQty),2) As ReturnRate, ")
                    'sb.AppendLine(" round(((1.0*ISNULL(B.TtlReturnDays ,0.0))/A.TtlQty ),2) as AvgDays ")
                    'sb.AppendLine(" from  #AA A ")
                    'sb.AppendLine(" left join  #BB  B on A.asset_owner =B.asset_owner  and A.assetdef_id =B.assetdef_id  ")
                    'sb.AppendLine(" left join auoref.dbo.emp_data_all C on A.asset_owner =C.emp_no collate Chinese_Taiwan_Stroke_CI_AS  ")
                    'sb.AppendLine(" left join C_AST_ASSETDEF  D on A.assetdef_id=D.def_id  ")
                    'sb.AppendLine(" where( @assetdef_id=0 or A.assetdef_id=@assetdef_id )")


                    'sb.AppendLine("and( @Dept=''or org_id=@Dept) ")

                    'sb.AppendLine(" drop table #AA ")
                    'sb.AppendLine(" drop table #BB ")
                    sb.AppendLine(" select  A.asset_owner, A.assetdef_id,isnull(C.emp_name,A.Asset_Owner) as emp_name , D.assetdef_nameEN as def_Name,")
                    sb.AppendLine("    A.TtlQty*1.0 as TtlQty, B.ReturnQty *1.0 as ReturnQty,")
                    sb.AppendLine("    round(((100.0*(ISNULL(B.ReturnQty ,0.0)))/A.TtlQty),2) As ReturnRate,")
                    sb.AppendLine("    round(((1.0*ISNULL(B.TtlReturnDays ,0.0))/A.TtlQty ),2) as AvgDays")
                    sb.AppendLine("from  (     select asset_owner ,assetdef_id ,COUNT(asset_serialNo )  as TtlQty")
                    sb.AppendLine("                from V_AST_ASSETALL ")
                    sb.AppendLine("    where not ReturnDeadLine is null  ")
                    sb.AppendLine("        and SendDate>=@ReturnDeadLine1 and SendDate<=@ReturnDeadLine2")
                    sb.AppendLine(" and (HouseId=@HouseID or @HouseID='0') ")
                    sb.AppendLine("    group by asset_owner ,assetdef_id ")
                    ' sb.AppendLine("    order by asset_owner ,assetdef_id ")
                    sb.AppendLine(") A      Left Join ")
                    sb.AppendLine("(     select asset_owner ,assetdef_id ,COUNT(asset_serialNo ) as ReturnQty,")
                    sb.AppendLine("        sum(datediff(day,  SendDate,ReturnDate))  as TtlReturnDays ")
                    sb.AppendLine("                from V_AST_ASSETALL  ")
                    sb.AppendLine("    where   not ReturnDate is null ")
                    sb.AppendLine("        and SendDate>=@ReturnDeadLine1 and SendDate<=@ReturnDeadLine2")
                    sb.AppendLine(" and (HouseId=@HouseID or @HouseID='0') ")
                    sb.AppendLine("    group by asset_owner ,assetdef_id ")
                    'sb.AppendLine("    order by asset_owner ,assetdef_id ")
                    sb.AppendLine(")   B on A.asset_owner =B.asset_owner  and A.assetdef_id =B.assetdef_id ")
                    sb.AppendLine("left join auoref.dbo.emp_data_all C on A.asset_owner =C.emp_no  ")
                    sb.AppendLine("left join C_AST_ASSETDEF  D on A.assetdef_id=D.def_id ")
                    sb.AppendLine("where( @assetdef_id=0 or A.assetdef_id=@assetdef_id ) and( @Dept=''or C.org_id like @Dept+'%')")

                    sb.AppendLine("order by C.org_id,A.asset_owner")



                    sqlParams(0) = New SqlParameter("@assetdef_id", SqlDbType.NVarChar)
                    sqlParams(0).Value = assetdef_id
                    sqlParams(1) = New SqlParameter("@Dept", SqlDbType.NVarChar)
                    sqlParams(1).Value = dept

                    sqlParams(2) = New SqlParameter("@ReturnDeadLine1", SqlDbType.DateTime)
                    sqlParams(2).Value = ReturnDeadLine1
                    sqlParams(3) = New SqlParameter("@ReturnDeadLine2", SqlDbType.DateTime)
                    sqlParams(3).Value = ReturnDeadLine2
                    sqlParams(4) = New SqlParameter("@HouseID", SqlDbType.NVarChar)
                    sqlParams(4).Value = HouseID
                    Dim dt1 As DataTable
                    Try
                        dt1 = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
                    Catch ex As Exception
                        ErrorLog("AssetDao", ex.Message, "GetReplyStatistics.Person1.dt1", "", "0", _dbName)
                    End Try



                    dt1.Columns.Add("Qty1")
                    dt1.Columns.Add("Qty21")
                    dt1.Columns.Add("Qty22")
                    dt1.Columns.Add("Qty4")
                    dt1.Columns.Add("Qty7")

                    For Each dr As DataRow In dt1.Rows
                        Dim owner As String = dr("asset_owner").ToString.Trim
                        Dim asset_id As String = dr("assetdef_id").ToString.Trim

                        If Not String.IsNullOrEmpty(owner) Then
                            dicAssetOwner(owner) = True
                        End If

                        If Not String.IsNullOrEmpty(asset_id) Then
                            dicAssetId(asset_id) = True
                        End If
                    Next

                    For Each s As String In dicAssetOwner.Keys
                        sbAssetOwner.AppendFormat("'{0}',", s)
                    Next

                    For Each s As String In dicAssetId.Keys
                        sbAssetId.AppendFormat("{0},", s)
                    Next

                    If sbAssetOwner.Length > 0 Then
                        sbAssetOwner.Remove(sbAssetOwner.Length - 1, 1)
                    End If

                    If sbAssetId.Length > 0 Then
                        sbAssetId.Remove(sbAssetId.Length - 1, 1)
                    End If

                    'If sbAssetOwner.Length = 0 OrElse sbAssetId.Length = 0 Then
                    '    Return dt1
                    'End If

                    Dim dt2 As DataTable
                    Try
                        dt2 = GetStatisticsPerson(ReturnDeadLine1, ReturnDeadLine2, HouseID, sbAssetOwner, sbAssetId)
                        'dt2 = GetStatisticsPerson(ReturnDeadLine1, ReturnDeadLine2, HouseID, dept, assetdef_id)
                    Catch ex As Exception
                        ErrorLog("AssetDao", ex.Message, "GetReplyStatistics:Person.dt2", "", "0", _dbName)
                    End Try

                    Dim drs() As DataRow

                    For Each dr As DataRow In dt1.Rows

                        If dr("asset_owner").ToString.Trim <> "" And dr("assetdef_id").ToString.Trim <> "" Then
                            drs = dt2.Select("asset_owner='" + dr("asset_owner").ToString.Trim + "' and assetdef_id=" + dr("assetdef_id").ToString.Trim)
                            If drs.Length > 0 Then
                                dr("Qty1") = drs(0)("Qty1").ToString
                                dr("Qty21") = drs(0)("Qty21").ToString
                                dr("Qty22") = drs(0)("Qty22").ToString
                                dr("Qty4") = drs(0)("Qty4").ToString
                                dr("Qty7") = drs(0)("Qty7").ToString
                            End If
                        End If

                    Next
                    Dim empDept As String
                    Dim dr3 As DataRow
                    For Each dr As DataRow In dt2.Rows
                        If dept = "" Then
                            If dr("assetdef_id").ToString.Trim <> "" Then

                                drs = dt1.Select("asset_owner='" + dr("asset_owner").ToString.Trim + "' and assetdef_id=" + dr("assetdef_id").ToString.Trim)
                                If drs.Length = 0 Then
                                    dr3 = dt1.NewRow()
                                    dr3("asset_owner") = dr("asset_owner")
                                    dr3("emp_name") = udao.GetUserName(dr("asset_owner").ToString)

                                    dr3("def_name") = GetAssetTypeName(dr("assetdef_id").ToString())
                                    dr3("assetdef_id") = dr("assetdef_id")
                                    dr3("Qty1") = dr("Qty1").ToString
                                    dr3("Qty21") = dr("Qty21").ToString
                                    dr3("Qty22") = dr("Qty22").ToString
                                    dr3("Qty4") = dr("Qty4").ToString
                                    dr3("Qty7") = dr("Qty7").ToString
                                    dt1.Rows.InsertAt(dr3, 0)
                                End If
                            End If
                        Else
                            Try
                                empDept = udao.GetEmpData(dr("asset_owner").ToString).Rows(0)("Dept_ID").ToString.Trim
                                If empDept.Length >= dept.Length Then
                                    If empDept.Substring(0, dept.Length).ToUpper = dept.ToUpper Then
                                        drs = dt1.Select("asset_owner='" + dr("asset_owner").ToString.Trim + "' and assetdef_id=" + dr("assetdef_id").ToString.Trim)
                                        If drs.Length = 0 Then
                                            dr3 = dt1.NewRow()
                                            dr3("asset_owner") = dr("asset_owner")
                                            dr3("emp_name") = udao.GetUserName(dr("asset_owner").ToString)

                                            dr3("def_name") = GetAssetTypeName(dr("assetdef_id").ToString())
                                            dr3("assetdef_id") = dr("assetdef_id")
                                            dr3("Qty1") = dr("Qty1").ToString
                                            dr3("Qty21") = dr("Qty21").ToString
                                            dr3("Qty22") = dr("Qty22").ToString
                                            dr3("Qty4") = dr("Qty4").ToString
                                            dr3("Qty7") = dr("Qty7").ToString
                                            dt1.Rows.InsertAt(dr3, 0)
                                        End If
                                    End If
                                End If

                            Catch ex As Exception

                            End Try


                        End If
                    Next

                    Return dt1
                End If
                If Kind = "1" Then

                    Dim sb As New StringBuilder()
                    Dim sqlParams As SqlParameter() = New SqlParameter(4) {}
                    'sb.AppendLine("  select org_id ,assetdef_id ,COUNT(asset_serialNo )  as TtlQty ")
                    'sb.AppendLine(" into #AA ")
                    'sb.AppendLine("  from V_AST_ASSETALL A ")
                    'sb.AppendLine(" left join auoref.dbo.emp_data_all B on A.asset_owner=B.emp_no  collate Chinese_Taiwan_Stroke_CI_AS  ")
                    'sb.AppendLine(" where not ReturnDeadLine is null  and asset_status='Distributed' ")
                    'sb.AppendLine(" and SendDate>=@ReturnDeadLine1 and SendDate<=@ReturnDeadLine2")
                    'sb.AppendLine(" group by org_id ,assetdef_id  ")
                    'sb.AppendLine(" order by org_id ,assetdef_id  ")
                    'sb.AppendLine(" select org_id ,assetdef_id ,COUNT(asset_serialNo ) as ReturnQty,sum(datediff(day,  SendDate,ReturnDate))  as TtlReturnDays  ")
                    'sb.AppendLine(" into #BB ")
                    'sb.AppendLine("  from V_AST_ASSETALL A ")
                    'sb.AppendLine(" left join auoref.dbo.emp_data_all B on A.asset_owner=B.emp_no  collate Chinese_Taiwan_Stroke_CI_AS  ")
                    'sb.AppendLine(" where  asset_status='Distributed' and ReturnBy <>'' ")
                    'sb.AppendLine(" and SendDate>=@ReturnDeadLine1 and SendDate<=@ReturnDeadLine2")

                    'sb.AppendLine(" group by org_id ,assetdef_id ,SendDate ")
                    'sb.AppendLine(" order by org_id ,assetdef_id  ")

                    'sb.AppendLine(" select A.org_id ,D.assetdef_nameEN as def_Name,A.TtlQty*1.0 as TtlQty, B.ReturnQty *1.0 as ReturnQty")
                    'sb.AppendLine(" ,round(((100.0*(ISNULL(B.ReturnQty ,0.0)))/A.TtlQty),2) As ReturnRate, ")
                    'sb.AppendLine(" round(((1.0*ISNULL(B.TtlReturnDays ,0.0))/A.TtlQty ),2) as AvgDays,A.assetdef_id ")
                    'sb.AppendLine(" from  #AA A ")
                    'sb.AppendLine(" left join  #BB  B on A.org_id =B.org_id  collate Chinese_Taiwan_Stroke_CI_AS    and A.assetdef_id =B.assetdef_id    ")

                    'sb.AppendLine(" left join C_AST_ASSETDEF  D on A.assetdef_id=D.def_id  ")
                    'sb.AppendLine(" where( @assetdef_id=0 or A.assetdef_id=@assetdef_id )")


                    'sb.AppendLine("and( @Dept=''or A.org_id=@Dept) ")

                    'sb.AppendLine(" drop table #AA ")
                    'sb.AppendLine(" drop table #BB ")




                    sb.AppendLine(" select   A.org_id ,D.assetdef_nameEN as def_Name,A.TtlQty*1.0 as TtlQty, B.ReturnQty *1.0 as ReturnQty")
                    sb.AppendLine(" ,round(((100.0*(ISNULL(B.ReturnQty ,0.0)))/A.TtlQty),2) As ReturnRate, ")
                    sb.AppendLine(" round(((1.0*ISNULL(B.TtlReturnDays ,0.0))/A.TtlQty ),2) as AvgDays,A.assetdef_id ")
                    sb.AppendLine(" from ( ")
                    sb.AppendLine("  select org_id ,assetdef_id ,COUNT(asset_serialNo )  as TtlQty ")
                    sb.AppendLine("  from V_AST_ASSETALL A ")
                    sb.AppendLine(" left join auoref.dbo.emp_data_all B on A.asset_owner=B.emp_no   ")
                    sb.AppendLine(" where not ReturnDeadLine is null   ")
                    sb.AppendLine(" and SendDate>=@ReturnDeadLine1 and SendDate<=@ReturnDeadLine2")
                    sb.AppendLine(" and (A.HouseId=@HouseID or @HouseID='0') ")
                    sb.AppendLine(" group by org_id ,assetdef_id  ")
                    sb.AppendLine(") A")
                    sb.AppendLine(" left join  ( ")
                    sb.AppendLine(" select org_id ,assetdef_id ,COUNT(asset_serialNo ) as ReturnQty,sum(datediff(day,  SendDate,ReturnDate))  as TtlReturnDays  ")
                    sb.AppendLine("  from V_AST_ASSETALL A ")
                    sb.AppendLine(" left join auoref.dbo.emp_data_all B on A.asset_owner=B.emp_no   ")
                    sb.AppendLine(" where    not ReturnDate is null ")
                    sb.AppendLine(" and SendDate>=@ReturnDeadLine1 and SendDate<=@ReturnDeadLine2")
                    sb.AppendLine(" and (A.HouseId=@HouseID or @HouseID='0') ")
                    sb.AppendLine(" group by org_id ,assetdef_id  ")
                    sb.AppendLine(") B on A.org_id =B.org_id    and A.assetdef_id =B.assetdef_id    ")
                    sb.AppendLine(" left join C_AST_ASSETDEF  D on A.assetdef_id=D.def_id  ")
                    sb.AppendLine(" where( @assetdef_id=0 or A.assetdef_id=@assetdef_id )")
                    sb.AppendLine("and( @Dept=''or A.org_id like @Dept+'%') ")

                    sb.AppendLine(" order by A.org_id ")

                    sqlParams(0) = New SqlParameter("@assetdef_id", SqlDbType.NVarChar)
                    sqlParams(0).Value = assetdef_id
                    sqlParams(1) = New SqlParameter("@Dept", SqlDbType.NVarChar)
                    sqlParams(1).Value = dept

                    sqlParams(2) = New SqlParameter("@ReturnDeadLine1", SqlDbType.DateTime)
                    sqlParams(2).Value = ReturnDeadLine1
                    sqlParams(3) = New SqlParameter("@ReturnDeadLine2", SqlDbType.DateTime)
                    sqlParams(3).Value = ReturnDeadLine2
                    sqlParams(4) = New SqlParameter("@HouseID", SqlDbType.NVarChar)
                    sqlParams(4).Value = HouseID
                    Dim dt1 As DataTable = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
                    Dim drs() As DataRow
                    dt1.Columns.Add("Qty1")
                    dt1.Columns.Add("Qty21")
                    dt1.Columns.Add("Qty22")
                    dt1.Columns.Add("Qty4")
                    dt1.Columns.Add("Qty7")


                    For Each dr As DataRow In dt1.Rows
                        Dim asset_id As String = dr("assetdef_id").ToString.Trim
                        Dim d As String = dr("org_id").ToString.Trim

                        If Not String.IsNullOrEmpty(asset_id) Then
                            dicAssetId(asset_id) = True
                        End If

                        If Not String.IsNullOrEmpty(d) Then
                            dicDept(d) = True
                        End If
                    Next

                    For Each s As String In dicAssetId.Keys
                        sbAssetId.AppendFormat("{0},", s)
                    Next

                    For Each s As String In dicDept.Keys
                        sbDept.AppendFormat("'{0}',", s)
                    Next

                    If sbAssetId.Length > 0 Then
                        sbAssetId.Remove(sbAssetId.Length - 1, 1)
                    End If

                    If sbDept.Length > 0 Then
                        sbDept.Remove(sbDept.Length - 1, 1)
                    End If

                    'If sbAssetId.Length = 0 OrElse sbDept.Length = 0 Then
                    '    Return dt1
                    'End If

                    Dim dt2 As DataTable = GetStatisticsDept(ReturnDeadLine1, ReturnDeadLine2, HouseID, sbAssetId, sbDept)
                    '  Dim dt2 As DataTable = GetStatisticsDept(ReturnDeadLine1, ReturnDeadLine2, HouseID, dept, assetdef_id)

                    For Each dr As DataRow In dt1.Rows
                        drs = dt2.Select("Dept='" + dr("org_id").ToString.Trim + "' and assetdef_id=" + dr("assetdef_id").ToString.Trim)
                        If drs.Length > 0 Then
                            dr("Qty1") = drs(0)("Qty1").ToString
                            dr("Qty21") = drs(0)("Qty21").ToString
                            dr("Qty22") = drs(0)("Qty22").ToString
                            dr("Qty4") = drs(0)("Qty4").ToString
                            dr("Qty7") = drs(0)("Qty7").ToString
                        End If
                    Next
                    Dim dr3 As DataRow
                    For Each dr As DataRow In dt2.Rows
                        If dept = "" Then
                            If dr("assetdef_id").ToString.Trim <> "" Then
                                If dr("Dept").ToString.Trim = "" Then
                                    drs = dt1.Select("(org_id='" + dr("Dept").ToString.Trim + "' or org_id is null ) and assetdef_id=" + dr("assetdef_id").ToString.Trim)
                                Else
                                    drs = dt1.Select("org_id='" + dr("Dept").ToString.Trim + "' and assetdef_id=" + dr("assetdef_id").ToString.Trim)
                                End If
                                'drs = dt1.Select("org_id='" + dr("Dept").ToString.Trim + "' and assetdef_id=" + dr("assetdef_id").ToString.Trim)
                                If drs.Length = 0 Then
                                    dr3 = dt1.NewRow()
                                    dr3("org_id") = dr("Dept")
                                    dr3("def_name") = GetAssetTypeName(dr("assetdef_id").ToString())
                                    dr3("assetdef_id") = dr("assetdef_id")
                                    dr3("Qty1") = dr("Qty1").ToString
                                    dr3("Qty21") = dr("Qty21").ToString
                                    dr3("Qty22") = dr("Qty22").ToString
                                    dr3("Qty4") = dr("Qty4").ToString
                                    dr3("Qty7") = dr("Qty7").ToString
                                    dt1.Rows.InsertAt(dr3, 0)
                                End If
                            End If
                        Else

                            If dr("Dept").ToString.Trim <> "" And dr("assetdef_id").ToString.Trim <> "" Then
                                If dr("Dept").ToString.Trim.Length >= dept.Length Then
                                    If dr("Dept").ToString.Trim.Substring(0, dept.Length).ToUpper = dept.ToUpper Then
                                        drs = dt1.Select("org_id='" + dr("Dept").ToString.Trim + "' and assetdef_id=" + dr("assetdef_id").ToString.Trim)
                                        If drs.Length = 0 Then
                                            dr3 = dt1.NewRow()
                                            dr3("org_id") = dr("Dept")
                                            dr3("def_name") = GetAssetTypeName(dr("assetdef_id").ToString())
                                            dr3("assetdef_id") = dr("assetdef_id")
                                            dr3("Qty1") = dr("Qty1").ToString
                                            dr3("Qty21") = dr("Qty21").ToString
                                            dr3("Qty22") = dr("Qty22").ToString
                                            dr3("Qty4") = dr("Qty4").ToString
                                            dr3("Qty7") = dr("Qty7").ToString
                                            dt1.Rows.InsertAt(dr3, 0)
                                        End If
                                    End If
                                End If


                            End If
                        End If


                    Next
                    Return dt1
                End If
            Catch ex As Exception
                ErrorLog("AssetDAO", ex.Message, "GetReplyStatistics", "", "0", _dbName)
            End Try
        End Function

        '  Function GetStatisticsDept(ByVal ModiDate1 As DateTime, ByVal ModiDate2 As DateTime, ByVal HouseID As String, ByVal sbAssetId As StringBuilder, ByVal sbDept As StringBuilder) As DataTable
        'Function GetStatisticsDept(ByVal ModiDate1 As DateTime, ByVal ModiDate2 As DateTime, ByVal HouseID As String, ByVal Dept As String, ByVal assetdef_id As String) As DataTable

        '    Dim cmd As MySqlCommand
        '    Dim con As MySqlConnection
        '    'Dim sb1 As New StringBuilder()
        '    Dim sb As New StringBuilder()
        '    Dim FileName As String = Now.ToString("HHmmssfffff")
        '    If assetdef_id = "" Then
        '        assetdef_id = "0"
        '    End If
        '    Try

        '        con = myMYSqlConn(_dbName)
        '        con.Open()
        '        cmd = New MySqlCommand(sb.ToString(), con)

        '        sb.AppendLine("create TEMPORARY Table  AAA0" + FileName + "( Dept varchar(100), assetdef_id int, Qty0 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA1" + FileName + " (Dept varchar(100),assetdef_id int,Qty1 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA21" + FileName + "(Dept varchar(100),assetdef_id int,Qty21 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA22" + FileName + "(Dept varchar(100),assetdef_id int,Qty22 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA4" + FileName + "(Dept varchar(100),assetdef_id int,Qty4 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA7" + FileName + "(Dept varchar(100),assetdef_id int,Qty7 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA6" + FileName + "(Dept varchar(100),assetdef_id int,Qty6 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAAA" + FileName + "(Dept varchar(100),assetdef_id int);")
        '        sb.AppendLine("create TEMPORARY Table AAAAA" + FileName + "(Dept varchar(100),assetdef_id int);")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        ' sb1.AppendLine("Insert into AAA" + FileName + " Select asset_SerialNo from v_ast_assetalllog where ModiType='6' ")
        '        ' sb1.AppendLine("Insert into AAA" + FileName + " Select asset_SerialNo from v_ast_assetalllog where 1=1 ")


        '        'sb1.AppendLine("and (assetdef_id =?assetdef_id or ?assetdef_id=0) ")

        '        'sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
        '        'sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0');")
        '        'cmd = New MySqlCommand(sb1.ToString(), con)
        '        'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        'cmd.Parameters.AddWithValue("?Dept", dept)
        '        'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        'cmd.Parameters.AddWithValue("?ModiDate2", ModiDate2)
        '        'cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        'cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA6" + FileName) ''繳回
        '        sb.AppendLine("select  FromDept,assetdef_id ,Count(distinct B.asset_serialNo) as Qty6  from   v_ast_assetalllog  B ")
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='4' and ifNull(isScrapped,'')=''")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by FromDept,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)

        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA0" + FileName + " select  Dept,assetdef_id ,Count(distinct B.asset_serialNo)  as Qty0 from    v_ast_assetalllog  B ") '新增
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='0'")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by Dept,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA1" + FileName + " select  Dept,assetdef_id ,Count(distinct B.asset_serialNo) as Qty1 from   v_ast_assetalllog  B ") '異動
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='1'")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by Dept,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA21" + FileName + " select  Dept,assetdef_id ,Count(distinct B.asset_serialNo) as Qty21 from   v_ast_assetalllog  B ") '移入
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='2' and Dept<>FromDept ")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by Dept,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA22" + FileName + " select  FromDept,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty22 from   v_ast_assetalllog  B  ") '轉出
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='2' and Dept<>FromDept")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by FromDept,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)

        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA4" + FileName + " select  FromDept,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty4 from  v_ast_assetalllog  B ") ''繳回
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='4'  and ifNull(isScrapped,'')='' ")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by FromDept,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA7" + FileName + " select  FromDept,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty7 from  v_ast_assetalllog  B ")
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='7' ")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by FromDept,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0
        '        sb.AppendLine(" insert into AAAA" + FileName + " select Dept,assetdef_id  from AAA6" + FileName + " ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA21" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA0" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA22" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA1" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA4" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA7" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAAA" + FileName + " select distinct Dept,assetdef_id from AAAA" + FileName + "; ")

        '        sb.AppendLine("select AA.*,A.Qty6,B.Qty0 ,C.Qty1  ,D.Qty21,E.Qty22,F.Qty4,G.Qty7")
        '        sb.AppendLine("from AAAAA" + FileName + " AA ")
        '        sb.AppendLine("left join  AAA6" + FileName + " A on AA.Dept=A.Dept and AA.assetdef_id =A.assetdef_id  ")
        '        sb.AppendLine("left join AAA0" + FileName + " B ON AA.Dept=B.Dept and AA.assetdef_id =B.assetdef_id ")
        '        sb.AppendLine("left join AAA1" + FileName + " C ON AA.Dept=C.Dept and AA.assetdef_id =C.assetdef_id")
        '        sb.AppendLine("left join AAA21" + FileName + " D ON AA.Dept=D.Dept and AA.assetdef_id =D.assetdef_id")
        '        sb.AppendLine("left join AAA22" + FileName + " E ON AA.Dept=E.Dept and AA.assetdef_id =E.assetdef_id")
        '        sb.AppendLine("left join AAA4" + FileName + " F ON AA.Dept=F.Dept and AA.assetdef_id =F.assetdef_id")
        '        sb.AppendLine("left join AAA7" + FileName + " G ON AA.Dept=G.Dept and AA.assetdef_id =G.assetdef_id;")
        '        sb.AppendLine("drop Table AAAAA" + FileName + "; drop Table AAAA" + FileName + ";drop Table AAA1" + FileName + ";drop Table AAA21" + FileName + ";drop Table AAA22" + FileName + ";drop Table AAA4" + FileName + ";drop Table AAA7" + FileName + ";drop Table AAA0" + FileName + ";drop Table AAA6" + FileName + ";")

        '        'add by Tim yang at 2011/07/22
        '        'sb.AppendLine("select Dept,assetdef_id, ")
        '        'sb.AppendLine("( ")
        '        'sb.AppendLine("	select count(distinct asset_serialNo) ")
        '        'sb.AppendLine("	from  v_ast_assetalllog ")
        '        'sb.AppendLine("	where Dept = B.Dept ")
        '        'sb.AppendLine("    and assetdef_id = B.assetdef_id ")
        '        'sb.AppendLine("		and ModiType='6' ")
        '        'sb.AppendLine("    and ModiDate>=?ModiDate1 ")
        '        'sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
        '        'sb.AppendLine(") AS Qty6, ")
        '        'sb.AppendLine("( ")
        '        'sb.AppendLine("	select count(distinct asset_serialNo) ")
        '        'sb.AppendLine("	from  v_ast_assetalllog ")
        '        'sb.AppendLine("	where Dept = B.Dept ")
        '        'sb.AppendLine("    and assetdef_id = B.assetdef_id ")
        '        'sb.AppendLine("		and ModiType='0'     ")
        '        'sb.AppendLine("		and ModiDate>=?ModiDate1 ")
        '        'sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
        '        'sb.AppendLine(") AS Qty0, ")
        '        'sb.AppendLine("( ")
        '        'sb.AppendLine("	select count(distinct asset_serialNo) ")
        '        'sb.AppendLine("	from  v_ast_assetalllog ")
        '        'sb.AppendLine("	where Dept = B.Dept ")
        '        'sb.AppendLine("    and assetdef_id = B.assetdef_id ")
        '        'sb.AppendLine("    and ModiType='1' ")
        '        'sb.AppendLine("		and ModiDate>=?ModiDate1 ")
        '        'sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
        '        'sb.AppendLine(") AS Qty1, ")
        '        'sb.AppendLine("( ")
        '        'sb.AppendLine("	select count(distinct asset_serialNo) ")
        '        'sb.AppendLine("	from  v_ast_assetalllog ")
        '        'sb.AppendLine("	where Dept = B.Dept ")
        '        'sb.AppendLine("    and assetdef_id = B.assetdef_id ")
        '        'sb.AppendLine("    and ModiType='2' ")
        '        'sb.AppendLine("    and ModiDate>=?ModiDate1 ")
        '        'sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  and FromDept<>Dept ")
        '        'sb.AppendLine(") AS Qty21, ")
        '        'sb.AppendLine("( ")
        '        'sb.AppendLine("	select Count(distinct asset_serialNo) as Qty ")
        '        'sb.AppendLine("	from  v_ast_assetalllog  ")
        '        'sb.AppendLine("	where  FromDept = B.Dept ")
        '        'sb.AppendLine("		and assetdef_id = B.assetdef_id ")
        '        'sb.AppendLine("		and ModiType='2' ")
        '        'sb.AppendLine("		and ModiDate>=?ModiDate1 ")
        '        'sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0') and FromDept<>Dept ")
        '        'sb.AppendLine(") Qty22, ")
        '        'sb.AppendLine("( ")
        '        'sb.AppendLine("	select count(distinct asset_serialNo) ")
        '        'sb.AppendLine("	from  v_ast_assetalllog ")
        '        'sb.AppendLine("	where Dept = B.Dept ")
        '        'sb.AppendLine("		and assetdef_id = B.assetdef_id ")
        '        'sb.AppendLine("		and ModiType='4' ")
        '        'sb.AppendLine("		and ModiDate>=?ModiDate1 ")
        '        'sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
        '        'sb.AppendLine(") AS Qty4, ")
        '        'sb.AppendLine("( ")
        '        'sb.AppendLine("	select count(distinct asset_serialNo) ")
        '        'sb.AppendLine("	from  v_ast_assetalllog ")
        '        'sb.AppendLine("	where Dept = B.Dept ")
        '        'sb.AppendLine("    and assetdef_id = B.assetdef_id ")
        '        'sb.AppendLine("    and ModiType='7' ")
        '        'sb.AppendLine("    and ModiDate>=?ModiDate1 ")
        '        'sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
        '        'sb.AppendLine(") AS Qty7 ")
        '        'sb.AppendLine("from  v_ast_assetalllog  B  ")
        '        'sb.AppendFormat("where assetdef_id IN ({0}) ", sbAssetId.ToString())
        '        'sb.AppendFormat(" and Dept IN ({0}) ", sbDept.ToString())
        '        ' sb.AppendLine("	and ModiDate>=?ModiDate1 ")
        '        'sb.AppendLine("	and (HouseId=?HouseID or ?HouseID='0')  ")
        '        'sb.AppendLine("group by Dept,assetdef_id  ")

        '        Dim aa As String
        '        Dim oTable As Data.DataTable
        '        aa = ""
        '        'con = myMYSqlConn(_dbName)
        '        'con.Open()
        '        cmd = New MySqlCommand(sb.ToString(), con)


        '        'aa = ""
        '        'cmd = New MySqlCommand(sb.ToString(), con)
        '        ''cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        ''cmd.Parameters.AddWithValue("?Dept", dept)
        '        'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        ''cmd.Parameters.AddWithValue("?ModiDate2", ModiDate2)
        '        'cmd.Parameters.AddWithValue("?HouseID", HouseID)

        '        'cmd.CommandTimeout = 50
        '        Dim oDbAdapter As MySqlDataAdapter
        '        oDbAdapter = New MySqlDataAdapter(cmd)
        '        'oDbAdapter.SelectCommand.CommandTimeout = 900
        '        oTable = New Data.DataTable()
        '        oDbAdapter.Fill(oTable)
        '        con.Close()
        '        Return oTable
        '    Catch ex As Exception
        '        con.Close()
        '        Throw ex
        '    Finally


        '    End Try
        'End Function
        Public Function StoreHouseIsExist(ByVal StroeHouseID As String) As Boolean
            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sb.AppendLine("  select count(asset_serialNo)  as TtlQty  from V_AST_ASSETALL  where storehouse=@storehouse")
            sqlParams(0) = New SqlParameter("@storehouse", SqlDbType.NVarChar)
            sqlParams(0).Value = StroeHouseID
            Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).ToString <> "0"
        End Function
        Public Function GetSerialNo() As String

            Dim sb As New StringBuilder()
            sb.AppendLine(" declare @maxSerial varchar(20)")
            sb.AppendLine(" select @maxSerial=MAX (asset_serialNo ) from [V_AST_ASSETALL]where asset_serialNo >='TW0' and asset_serialNo <'TW999999999'")
            sb.AppendLine(" declare  @max1 int set @max1=CONVERT(int,substring(@maxSerial,3,9))+1 set @maxSerial='TW'+convert(varchar(9),@max1)  select @maxSerial ")
            Return SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString()).ToString()
        End Function

        'Function GetStatisticsPerson(ByVal ModiDate1 As DateTime, ByVal ModiDate2 As DateTime, ByVal HouseID As String, ByVal Dept As String, ByVal assetdef_id As String) As DataTable
        '    Dim cmd As MySqlCommand
        '    Dim con As MySqlConnection
        '    'Dim sb1 As New StringBuilder()
        '    Dim sb As New StringBuilder()
        '    Dim FileName As String = Now.ToString("HHmmssfffff")
        '    If assetdef_id = "" Then
        '        assetdef_id = "0"
        '    End If
        '    Try

        '        con = myMYSqlConn(_dbName)
        '        con.Open()
        '        cmd = New MySqlCommand(sb.ToString(), con)

        '        sb.AppendLine("create TEMPORARY Table  AAA0" + FileName + "( Asset_Owner varchar(100), assetdef_id int, Qty0 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA1" + FileName + " (Asset_Owner varchar(100),assetdef_id int,Qty1 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA21" + FileName + "(Asset_Owner varchar(100),assetdef_id int,Qty21 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA22" + FileName + "(Asset_Owner varchar(100),assetdef_id int,Qty22 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA4" + FileName + "(Asset_Owner varchar(100),assetdef_id int,Qty4 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA7" + FileName + "(Asset_Owner varchar(100),assetdef_id int,Qty7 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAA6" + FileName + "(Asset_Owner varchar(100),assetdef_id int,Qty6 Int);")
        '        sb.AppendLine("create TEMPORARY Table  AAAA" + FileName + "(Asset_Owner varchar(100),assetdef_id int);")
        '        sb.AppendLine("create TEMPORARY Table AAAAA" + FileName + "(Asset_Owner varchar(100),assetdef_id int);")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA6" + FileName) ''繳回
        '        sb.AppendLine("select  FromAsset_Owner,assetdef_id ,Count(distinct B.asset_serialNo) as Qty6  from   v_ast_assetalllog  B ")
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='4'and ifNull(isScrapped,'')='' ")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by FromAsset_Owner,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)

        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA0" + FileName + " select  Asset_Owner,assetdef_id ,Count(distinct B.asset_serialNo)  as Qty0 from    v_ast_assetalllog  B ") '新增
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='0'")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by Asset_Owner,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA1" + FileName + " select  Asset_Owner,assetdef_id ,Count(distinct B.asset_serialNo) as Qty1 from   v_ast_assetalllog  B ") '異動
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='1'")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by Asset_Owner,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA21" + FileName + " select  Asset_Owner,assetdef_id ,Count(distinct B.asset_serialNo) as Qty21 from   v_ast_assetalllog  B ") '移入
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")

        '        sb.AppendLine("and (HouseId=?HouseID  or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='2' and Asset_Owner<>FromAsset_Owner ")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by Asset_Owner,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA22" + FileName + " select  FromAsset_Owner,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty22 from   v_ast_assetalllog  B  ") '轉出
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='2' and Asset_Owner<>FromAsset_Owner")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by FromAsset_Owner,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)

        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA4" + FileName + " select  FromAsset_Owner,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty4 from  v_ast_assetalllog  B ") ''繳回
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='4' and ifNull(isScrapped,'')='' ")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by FromAsset_Owner,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0

        '        sb.AppendLine("insert into AAA7" + FileName + " select  FromAsset_Owner,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty7 from  v_ast_assetalllog  B ")
        '        sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
        '        sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
        '        sb.AppendLine("and (Dept like '" + Dept + "%'  or FromDept like '" + Dept + "%' or ?Dept='') ")
        '        sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
        '        sb.AppendLine("and  B.ModiType='7'")
        '        sb.AppendLine("and B.ModiDate>=?ModiDate1")
        '        sb.AppendLine("group by FromAsset_Owner,assetdef_id ;")
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
        '        cmd.Parameters.AddWithValue("?Dept", Dept)
        '        cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
        '        cmd.Parameters.AddWithValue("?HouseID", HouseID)
        '        cmd.CommandTimeout = 0
        '        cmd.ExecuteNonQuery()
        '        sb.Length = 0
        '        sb.AppendLine(" insert into AAAA" + FileName + " select Asset_Owner,assetdef_id  from AAA6" + FileName + " ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Asset_Owner,A.assetdef_id  from AAA21" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Asset_Owner,A.assetdef_id  from AAA0" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Asset_Owner,A.assetdef_id  from AAA22" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Asset_Owner,A.assetdef_id  from AAA1" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Asset_Owner,A.assetdef_id  from AAA4" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAA" + FileName + " select A.Asset_Owner,A.assetdef_id  from AAA7" + FileName + " A ;")
        '        sb.AppendLine(" insert into AAAAA" + FileName + " select distinct Asset_Owner,assetdef_id from AAAA" + FileName + "; ")
        '        sb.AppendLine("select AA.*,A.Qty6,B.Qty0 ,C.Qty1  ,D.Qty21,E.Qty22,F.Qty4,G.Qty7")
        '        sb.AppendLine("from AAAAA" + FileName + " AA ")
        '        sb.AppendLine("left join  AAA6" + FileName + " A on AA.Asset_Owner=A.Asset_Owner and AA.assetdef_id =A.assetdef_id  ")
        '        sb.AppendLine("left join AAA0" + FileName + " B ON AA.Asset_Owner=B.Asset_Owner and AA.assetdef_id =B.assetdef_id ")
        '        sb.AppendLine("left join AAA1" + FileName + " C ON AA.Asset_Owner=C.Asset_Owner and AA.assetdef_id =C.assetdef_id")
        '        sb.AppendLine("left join AAA21" + FileName + " D ON AA.Asset_Owner=D.Asset_Owner and AA.assetdef_id =D.assetdef_id")
        '        sb.AppendLine("left join AAA22" + FileName + " E ON AA.Asset_Owner=E.Asset_Owner and AA.assetdef_id =E.assetdef_id")
        '        sb.AppendLine("left join AAA4" + FileName + " F ON AA.Asset_Owner=F.Asset_Owner and AA.assetdef_id =F.assetdef_id")
        '        sb.AppendLine("left join AAA7" + FileName + " G ON AA.Asset_Owner=G.Asset_Owner and AA.assetdef_id =G.assetdef_id;")
        '        sb.AppendLine("drop Table AAAAA" + FileName + "; drop Table AAAA" + FileName + ";drop Table AAA1" + FileName + ";drop Table AAA21" + FileName + ";drop Table AAA22" + FileName + ";drop Table AAA4" + FileName + ";drop Table AAA7" + FileName + ";drop Table AAA0" + FileName + ";drop Table AAA6" + FileName + ";")




        '        Dim aa As String
        '        Dim oTable As Data.DataTable
        '        aa = ""
        '        cmd = New MySqlCommand(sb.ToString(), con)
        '        Dim oDbAdapter As MySqlDataAdapter
        '        oDbAdapter = New MySqlDataAdapter(cmd)
        '        oTable = New Data.DataTable()
        '        oDbAdapter.Fill(oTable)
        '        con.Close()
        '        Return oTable
        '    Catch ex As Exception
        '        con.Close()
        '        Throw ex
        '    Finally


        '    End Try
        'End Function
        Function GetStatisticsPerson(ByVal ModiDate1 As DateTime, ByVal ModiDate2 As DateTime, ByVal HouseID As String, ByVal sbAssetOnwer As StringBuilder, ByVal sbAssetId As StringBuilder) As DataTable

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim sb As New StringBuilder()
            Dim FileName As String = Now.ToString("HHmmssfffff")

            'If assetdef_id = "" Then
            '    assetdef_id = "0"
            'End If

            Try
                con.Open()
                'sb.AppendLine(" create TEMPORARY Table AAAA" + FileName + "(asset_owner varchar(100),assetdef_id int);")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" create TEMPORARY Table AAAAA" + FileName + "(asset_owner varchar(100),assetdef_id int);")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" create TEMPORARY Table AAA0" + FileName + "(asset_owner varchar(100),assetdef_id int,Qty0 int );")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" create TEMPORARY Table AAA1" + FileName + "(asset_owner varchar(100),assetdef_id int,Qty1 int );")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" create TEMPORARY Table AAA21" + FileName + "(asset_owner varchar(100),assetdef_id int,Qty21 int );")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" create TEMPORARY Table AAA22" + FileName + "(asset_owner varchar(100),assetdef_id int,Qty22 int );")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" create TEMPORARY Table AAA4" + FileName + "(asset_owner varchar(100),assetdef_id int,Qty4 int );")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" create TEMPORARY Table AAA7" + FileName + "(asset_owner varchar(100),assetdef_id int,Qty7 int );")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" create TEMPORARY Table AAA6" + FileName + "(asset_owner varchar(100),assetdef_id int,Qty6 int );")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.ExecuteNonQuery()
                'sb.Length = 0

                'sb.AppendLine(" insert into AAA6" + FileName + " select asset_owner,assetdef_id,Count(distinct B.asset_serialNo) as Qty6 ")
                'sb.AppendLine(" from    v_ast_assetalllog B  ")
                'sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb.AppendLine(" and  B.ModiType='6'")
                'sb.AppendLine(" and B.ModiDate>=?ModiDate1")
                'sb.AppendLine(" group by asset_owner,assetdef_id ;")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" insert into AAA0" + FileName + " select asset_owner,assetdef_id,Count(distinct B.asset_serialNo ) as Qty0 from  v_ast_assetalllog  B ")
                'sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb.AppendLine(" and B.ModiType='0'")
                'sb.AppendLine(" and B.ModiDate>=?ModiDate1")
                'sb.AppendLine(" group by asset_owner,assetdef_id;")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" insert into AAA1" + FileName + " select asset_owner,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty1 from   v_ast_assetalllog  B  ")
                'sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb.AppendLine("and B.ModiType='1'")
                'sb.AppendLine(" and B.ModiDate>=?ModiDate1")
                'sb.AppendLine(" group by asset_owner,assetdef_id ;")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" insert into AAA21" + FileName + " select asset_owner,assetdef_id ,Count(distinct B.asset_serialNo) as Qty21 from   v_ast_assetalllog  B ")
                'sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb.Append(" and B.ModiType='2'")
                'sb.AppendLine(" and B.ModiDate>=?ModiDate1")
                'sb.AppendLine(" group by asset_owner,assetdef_id ;")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb.Length = 0

                'sb.AppendLine(" insert into AAA22" + FileName + " select Fromasset_owner,assetdef_id ,Count(distinct B.asset_serialNo) as Qty22 from  v_ast_assetalllog  B ")
                'sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb.AppendLine(" and B.ModiType='2'")
                'sb.AppendLine(" and B.ModiDate>=?ModiDate1")
                'sb.AppendLine(" group by Fromasset_owner,assetdef_id ;")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" insert into AAA4" + FileName + " select asset_owner,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty4 from  v_ast_assetalllog  B ")
                'sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb.AppendLine("and B.ModiType='4'")
                'sb.AppendLine(" and B.ModiDate>=?ModiDate1")
                'sb.AppendLine(" group by asset_owner,assetdef_id ;")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" insert into AAA7" + FileName + "")
                'sb.AppendLine(" select  asset_owner,assetdef_id ,Count(distinct B.asset_serialNo) as Qty7 from  v_ast_assetalllog  B  ")
                'sb.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb.AppendLine("  and B.ModiType='7'")
                'sb.AppendLine(" and B.ModiDate>=?ModiDate1")
                'sb.AppendLine(" group by asset_owner,assetdef_id ;")
                'cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb.Length = 0
                'sb.AppendLine(" insert into AAAA" + FileName + " select asset_owner,assetdef_id  from AAA6" + FileName + " ;")
                'sb.AppendLine(" insert into AAAA" + FileName + " select A.asset_owner,A.assetdef_id  from AAA21" + FileName + " A ;")
                'sb.AppendLine(" insert into AAAA" + FileName + " select A.asset_owner,A.assetdef_id  from AAA0" + FileName + " A ;")
                'sb.AppendLine(" insert into AAAA" + FileName + " select A.asset_owner,A.assetdef_id  from AAA22" + FileName + " A ;")
                'sb.AppendLine(" insert into AAAA" + FileName + " select A.asset_owner,A.assetdef_id  from AAA1" + FileName + " A ;")
                'sb.AppendLine(" insert into AAAA" + FileName + " select A.asset_owner,A.assetdef_id  from AAA4" + FileName + " A ;")
                'sb.AppendLine(" insert into AAAA" + FileName + " select A.asset_owner,A.assetdef_id  from AAA7" + FileName + " A ;")
                'sb.AppendLine(" insert into AAAAA" + FileName + " select distinct asset_owner,assetdef_id from AAAA" + FileName + "; ")
                'sb.AppendLine(" select AA.*,A.Qty6 ,B.Qty0 ,C.Qty1  ,D.Qty21,E.Qty22,F.Qty4,G.Qty7 ")
                'sb.AppendLine(" from AAAAA" + FileName + " AA ")
                'sb.AppendLine(" left join AAA6" + FileName + " A ON AA.asset_owner=A.asset_owner and AA.assetdef_id =A.assetdef_id ")
                'sb.AppendLine(" left join AAA0" + FileName + " B ON AA.asset_owner=B.asset_owner and AA.assetdef_id =B.assetdef_id ")
                'sb.AppendLine(" left join AAA1" + FileName + " C ON AA.asset_owner=C.asset_owner and AA.assetdef_id =C.assetdef_id ")
                'sb.AppendLine(" left join AAA21" + FileName + " D ON AA.asset_owner=D.asset_owner and AA.assetdef_id =D.assetdef_id")
                'sb.AppendLine(" left join AAA22" + FileName + " E ON AA.asset_owner=E.asset_owner and AA.assetdef_id =E.assetdef_id")
                'sb.AppendLine(" left join AAA4" + FileName + " F ON AA.asset_owner=F.asset_owner and AA.assetdef_id =F.assetdef_id")
                'sb.AppendLine(" left join AAA7" + FileName + " G ON AA.asset_owner=G.asset_owner and AA.assetdef_id =G.assetdef_id")
                'sb.AppendLine(" WHERE ifnull(AA.asset_owner,'')<>'' and ifnull(AA.assetdef_id,0)<>0")
                'sb.AppendLine(" order by AA.asset_owner,AA.assetdef_id ;")
                'sb.AppendLine("drop Table AAAA" + FileName + ";drop Table AAAAA" + FileName + ";drop Table AAA1" + FileName + ";drop Table AAA21" + FileName + ";drop Table AAA22" + FileName + ";drop Table AAA4" + FileName + ";")
                'sb.AppendLine(" drop Table AAA7" + FileName + ";drop Table AAA0" + FileName + ";drop Table AAA6" + FileName + ";")

                'add by Tim Yang at 2011/07/22
                sb.AppendLine("select asset_owner,assetdef_id, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where asset_owner = B.asset_owner ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and ModiType='6' ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty6, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where asset_owner = B.asset_owner ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and ModiType='0' ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty0, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where asset_owner = B.asset_owner ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and ModiType='1' ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty1, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where asset_owner = B.asset_owner ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and ModiType='2' ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty21, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select Count(distinct asset_serialNo) as Qty ")
                sb.AppendLine("	from  v_ast_assetalllog  ")
                sb.AppendLine("	where  Fromasset_owner = B.asset_owner ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and ModiType='2' ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") Qty22, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where asset_owner = B.asset_owner ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and ModiType='4' ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty4, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where asset_owner = B.asset_owner ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and ModiType='7' ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty7 ")
                sb.AppendLine("from  v_ast_assetalllog  B  ")
                sb.AppendFormat("where assetdef_id IN ({0}) ", sbAssetId.ToString())
                sb.AppendFormat("   and asset_owner IN ({0}) ", sbAssetOnwer.ToString())
                sb.AppendLine("	    and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine("group by asset_owner,assetdef_id  ")

                Dim oTable As Data.DataTable

                cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?ModiDate2", ModiDate2)
                cmd.Parameters.AddWithValue("?HouseID", HouseID)

                cmd.CommandTimeout = 0
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()

            End Try
        End Function
        Function GetStatisticsDept(ByVal ModiDate1 As DateTime, ByVal ModiDate2 As DateTime, ByVal HouseID As String, ByVal sbAssetId As StringBuilder, ByVal sbDept As StringBuilder) As DataTable
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            'Dim sb1 As New StringBuilder()
            Dim sb As New StringBuilder()
            Dim FileName As String = Now.ToString("HHmmssfffff")
            'If assetdef_id = "" Then
            '    assetdef_id = "0"
            'End If
            Try
                con.Open()

                'sb1.AppendLine("create TEMPORARY Table  AAA0" + FileName + "( Dept varchar(100), assetdef_id int, Qty0 Int);")
                'sb1.AppendLine("create TEMPORARY Table  AAA1" + FileName + " (Dept varchar(100),assetdef_id int,Qty1 Int);")
                'sb1.AppendLine("create TEMPORARY Table  AAA21" + FileName + "(Dept varchar(100),assetdef_id int,Qty21 Int);")
                'sb1.AppendLine("create TEMPORARY Table  AAA22" + FileName + "(Dept varchar(100),assetdef_id int,Qty22 Int);")
                'sb1.AppendLine("create TEMPORARY Table  AAA4" + FileName + "(Dept varchar(100),assetdef_id int,Qty4 Int);")
                'sb1.AppendLine("create TEMPORARY Table  AAA7" + FileName + "(Dept varchar(100),assetdef_id int,Qty7 Int);")
                'sb1.AppendLine("create TEMPORARY Table  AAA6" + FileName + "(Dept varchar(100),assetdef_id int,Qty6 Int);")
                'sb1.AppendLine("create TEMPORARY Table  AAAA" + FileName + "(Dept varchar(100),assetdef_id int);")
                'sb1.AppendLine("create TEMPORARY Table AAAAA" + FileName + "(Dept varchar(100),assetdef_id int);")
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb1.Length = 0

                '' sb1.AppendLine("Insert into AAA" + FileName + " Select asset_SerialNo from v_ast_assetalllog where ModiType='6' ")
                '' sb1.AppendLine("Insert into AAA" + FileName + " Select asset_SerialNo from v_ast_assetalllog where 1=1 ")


                ''sb1.AppendLine("and (assetdef_id =?assetdef_id or ?assetdef_id=0) ")

                ''sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
                ''sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0');")
                ''cmd = New MySqlCommand(sb1.ToString(), con)
                ''cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                ''cmd.Parameters.AddWithValue("?Dept", dept)
                ''cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                ''cmd.Parameters.AddWithValue("?ModiDate2", ModiDate2)
                ''cmd.Parameters.AddWithValue("?HouseID", HouseID)
                ''cmd.ExecuteNonQuery()
                'sb1.Length = 0

                'sb1.AppendLine("insert into AAA6" + FileName)
                'sb1.AppendLine("select  Dept,assetdef_id ,Count(distinct B.asset_serialNo) as Qty6  from   v_ast_assetalllog  B ")
                'sb1.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb1.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb1.AppendLine("and  B.ModiType='6'")
                'sb1.AppendLine("and B.ModiDate>=?ModiDate1")
                'sb1.AppendLine("group by Dept,assetdef_id ;")
                'cmd = New MySqlCommand(sb1.ToString(), con)

                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb1.Length = 0

                'sb1.AppendLine("insert into AAA0" + FileName + " select  Dept,assetdef_id ,Count(distinct B.asset_serialNo)  as Qty0 from    v_ast_assetalllog  B ")
                'sb1.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb1.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb1.AppendLine("and  B.ModiType='0'")
                'sb1.AppendLine("and B.ModiDate>=?ModiDate1")
                'sb1.AppendLine("group by Dept,assetdef_id ;")
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb1.Length = 0

                'sb1.AppendLine("insert into AAA1" + FileName + " select  Dept,assetdef_id ,Count(distinct B.asset_serialNo) as Qty1 from   v_ast_assetalllog  B ")
                'sb1.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb1.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb1.AppendLine("and  B.ModiType='1'")
                'sb1.AppendLine("and B.ModiDate>=?ModiDate1")
                'sb1.AppendLine("group by Dept,assetdef_id ;")
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb1.Length = 0

                'sb1.AppendLine("insert into AAA21" + FileName + " select  Dept,assetdef_id ,Count(distinct B.asset_serialNo) as Qty21 from   v_ast_assetalllog  B ")
                'sb1.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb1.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb1.AppendLine("and  B.ModiType='2'")
                'sb1.AppendLine("and B.ModiDate>=?ModiDate1")
                'sb1.AppendLine("group by Dept,assetdef_id ;")
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb1.Length = 0

                'sb1.AppendLine("insert into AAA22" + FileName + " select  FromDept,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty22 from   v_ast_assetalllog  B  ")
                'sb1.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb1.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb1.AppendLine("and  B.ModiType='2'")
                'sb1.AppendLine("and B.ModiDate>=?ModiDate1")
                'sb1.AppendLine("group by FromDept,assetdef_id ;")
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)

                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb1.Length = 0

                'sb1.AppendLine("insert into AAA4" + FileName + " select  Dept,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty4 from  v_ast_assetalllog  B ")
                'sb1.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb1.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb1.AppendLine("and  B.ModiType='4'")
                'sb1.AppendLine("and B.ModiDate>=?ModiDate1")
                'sb1.AppendLine("group by Dept,assetdef_id ;")
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb1.Length = 0

                'sb1.AppendLine("insert into AAA7" + FileName + " select  Dept,assetdef_id ,Count(distinct B.asset_serialNo ) as Qty7 from  v_ast_assetalllog  B ")
                'sb1.AppendLine(" where B.asset_serialNo in (Select distinct asset_SerialNo from v_ast_assetalllog  where ")
                'sb1.AppendLine(" (assetdef_id =?assetdef_id or ?assetdef_id=0) ")
                'sb1.AppendLine("and (Dept=?Dept or ?Dept='') ")
                'sb1.AppendLine("and (HouseId=?HouseID or ?HouseID='0') )")
                'sb1.AppendLine("and  B.ModiType='7'")
                'sb1.AppendLine("and B.ModiDate>=?ModiDate1")
                'sb1.AppendLine("group by Dept,assetdef_id ;")
                'cmd = New MySqlCommand(sb1.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                'cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?HouseID", HouseID)
                'cmd.CommandTimeout = 0
                'cmd.ExecuteNonQuery()
                'sb1.Length = 0
                'sb1.AppendLine(" insert into AAAA" + FileName + " select Dept,assetdef_id  from AAA6" + FileName + " ;")
                'sb1.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA21" + FileName + " A ;")
                'sb1.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA0" + FileName + " A ;")
                'sb1.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA22" + FileName + " A ;")
                'sb1.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA1" + FileName + " A ;")
                'sb1.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA4" + FileName + " A ;")
                'sb1.AppendLine(" insert into AAAA" + FileName + " select A.Dept,A.assetdef_id  from AAA7" + FileName + " A ;")
                'sb1.AppendLine(" insert into AAAAA" + FileName + " select distinct Dept,assetdef_id from AAAA" + FileName + "; ")

                'sb1.AppendLine("select AA.*,A.Qty6,B.Qty0 ,C.Qty1  ,D.Qty21,E.Qty22,F.Qty4,G.Qty7")
                'sb1.AppendLine("from AAAAA" + FileName + " AA ")
                'sb1.AppendLine("left join  AAA6" + FileName + " A on AA.Dept=A.Dept and AA.assetdef_id =A.assetdef_id  ")
                'sb1.AppendLine("left join AAA0" + FileName + " B ON AA.Dept=B.Dept and AA.assetdef_id =B.assetdef_id ")
                'sb1.AppendLine("left join AAA1" + FileName + " C ON AA.Dept=C.Dept and AA.assetdef_id =C.assetdef_id")
                'sb1.AppendLine("left join AAA21" + FileName + " D ON AA.Dept=D.Dept and AA.assetdef_id =D.assetdef_id")
                'sb1.AppendLine("left join AAA22" + FileName + " E ON AA.Dept=E.Dept and AA.assetdef_id =E.assetdef_id")
                'sb1.AppendLine("left join AAA4" + FileName + " F ON AA.Dept=F.Dept and AA.assetdef_id =F.assetdef_id")
                'sb1.AppendLine("left join AAA7" + FileName + " G ON AA.Dept=G.Dept and AA.assetdef_id =G.assetdef_id;")
                'sb1.AppendLine("drop Table AAAAA" + FileName + "; drop Table AAAA" + FileName + ";drop Table AAA1" + FileName + ";drop Table AAA21" + FileName + ";drop Table AAA22" + FileName + ";drop Table AAA4" + FileName + ";drop Table AAA7" + FileName + ";drop Table AAA0" + FileName + ";drop Table AAA6" + FileName + ";")

                'add by Tim yang at 2011/07/22
                sb.AppendLine("select Dept,assetdef_id, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where Dept = B.Dept ")
                sb.AppendLine("    and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiType='6' ")
                sb.AppendLine("    and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty6, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where Dept = B.Dept ")
                sb.AppendLine("    and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiType='0'     ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty0, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where Dept = B.Dept ")
                sb.AppendLine("    and assetdef_id = B.assetdef_id ")
                sb.AppendLine("    and ModiType='1' ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty1, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where Dept = B.Dept ")
                sb.AppendLine("    and assetdef_id = B.assetdef_id ")
                sb.AppendLine("    and ModiType='2' ")
                sb.AppendLine("    and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty21, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select Count(distinct asset_serialNo) as Qty ")
                sb.AppendLine("	from  v_ast_assetalllog  ")
                sb.AppendLine("	where  FromDept = B.Dept ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiType='2' ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") Qty22, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where Dept = B.Dept ")
                sb.AppendLine("		and assetdef_id = B.assetdef_id ")
                sb.AppendLine("		and ModiType='4' ")
                sb.AppendLine("		and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty4, ")
                sb.AppendLine("( ")
                sb.AppendLine("	select count(distinct asset_serialNo) ")
                sb.AppendLine("	from  v_ast_assetalllog ")
                sb.AppendLine("	where Dept = B.Dept ")
                sb.AppendLine("    and assetdef_id = B.assetdef_id ")
                sb.AppendLine("    and ModiType='7' ")
                sb.AppendLine("    and ModiDate>=?ModiDate1 ")
                sb.AppendLine("		and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine(") AS Qty7 ")
                sb.AppendLine("from  v_ast_assetalllog  B  ")
                sb.AppendFormat("where assetdef_id IN ({0}) ", sbAssetId.ToString())
                sb.AppendFormat(" and Dept IN ({0}) ", sbDept.ToString())
                sb.AppendLine("	and ModiDate>=?ModiDate1 ")
                sb.AppendLine("	and (HouseId=?HouseID or ?HouseID='0')  ")
                sb.AppendLine("group by Dept,assetdef_id  ")


                Dim oTable As Data.DataTable

                'cmd = New MySqlCommand(sb1.ToString(), con)
                cmd = New MySqlCommand(sb.ToString(), con)
                'cmd.Parameters.AddWithValue("?assetdef_id", assetdef_id)
                'cmd.Parameters.AddWithValue("?Dept", dept)
                cmd.Parameters.AddWithValue("?ModiDate1", ModiDate1)
                'cmd.Parameters.AddWithValue("?ModiDate2", ModiDate2)
                cmd.Parameters.AddWithValue("?HouseID", HouseID)

                cmd.CommandTimeout = 0
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()

            End Try
        End Function


        Public Function GetAssetLogFromOldSystem() As DataTable
            Dim sb As New StringBuilder()
            sb.AppendLine(" select * from   v_asset_log  where SSIS='' ")
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        End Function
        Public Sub UpdateOldSystemLog(ByVal ID As Integer)
            Dim sb As New StringBuilder()
            sb.AppendLine(" update   v_asset_log set SSIS='Y'  where ID = " + ID.ToString)
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString())
        End Sub
        Public Sub InsertAssetLogByOldSystem(ByVal dr As DataRow, ByVal ModiType As String)

            Dim sb As New StringBuilder()
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            Dim logDao As AssetLogDAO = New AssetLogDAO(_dbName)


            ''新增到mySql中
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Dim sb1 As New StringBuilder()


            Try
                sb1.AppendLine(" select max(SeqNo)+1 as MAX1 from v_ast_assetalllog where asset_serialNo=?asset_serialNo ")

                Dim oTable As Data.DataTable

                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", dr("asset_serialNo").ToString)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Dim max As Integer
                If oTable.Rows(0)("MAX1").ToString <> "" Then
                    max = Integer.Parse(oTable.Rows(0)("MAX1").ToString)
                Else
                    max = 1
                End If
                con.Open()
                sb1.Length = 0
                Dim field As String = "asset_serialNo, ModiType, SeqNo,ModiUID,ModiDate"
                Dim field1 As String = "?asset_serialNo,?ModiType,?SeqNo,?ModiUID,?ModiDate"

                ''===0
                field += ",assetdef_id"
                field1 += ",?assetdef_id"

                field += ",asset_owner"
                field1 += ",?asset_owner"

                field += ",asset_status"
                field1 += ",?asset_status"
                field += ",Class"
                field1 += ",?Class"


                field += ",EXt"
                field1 += ",?EXt"
                ''====10
                field += ",User1"
                field1 += ",?User1"
                field += ",Dept"
                field1 += ",?Dept"
                field += ",Location"
                field1 += ",?Location"
                field += ",ComputerName"
                field1 += ",?ComputerName"
                field += ",Model"
                field1 += ",?Model"
                field += ",CPU"
                field1 += ",?CPU"


                field += ",FDD"
                field1 += ",?FDD"


                field += ",CD_R"
                field1 += ",?CD_R"

                field += ",CD_RW"
                field1 += ",?CD_RW"
                field += ",MAC1"
                field1 += ",?MAC1"
                ''20
                field += ",MAC2"
                field1 += ",?MAC2"
                field += ",PS"
                field1 += ",?PS"

                field += ",NBLabel"
                field1 += ",?NBLabel"
                field += ",Remark"
                field1 += ",?Remark"
                field += ",OS"
                field1 += ",?OS"

                field += ",HDD"
                field1 += ",?HDD"
                field += ",RAM"
                field1 += ",?RAM"
                field += ",UsingType"
                field1 += ",?UsingType"

                field += ",MonitorSize"
                field1 += ",?MonitorSize"
                field += ",Factory"
                field1 += ",?Factory"
                ''30
                field += ",storehouse"
                field1 += ",?storehouse"
                field += ",StoreLocation"
                field1 += ",?StoreLocation"
                field += ",UsingType2"
                field1 += ",?UsingType2"
                field += ",HouseID"
                field1 += ",?HouseID"
                field += ",OtherRemark"
                field1 += ",?OtherRemark"

                field += ",UsingDesc"
                field1 += ",?UsingDesc"
                ''30
                If dr("assetdef_idChg").ToString.Trim = "Y" Then
                    field += ",assetdef_idChg"
                    field1 += ",?assetdef_idChg"
                End If
                If dr("asset_ownerChg").ToString.Trim = "Y" Then '''
                    field += ",asset_ownerChg"
                    field1 += ",?asset_ownerChg"
                End If
                field += ",FromDept,FromAsset_Owner"
                field1 += ",?FromDept,?FromAsset_Owner"

                '40:
                If dr("asset_statusChg").ToString.Trim = "Y" Then
                    field += ",asset_statusChg"
                    field1 += ",?asset_statusChg"
                End If



                If dr("ClassChg").ToString.Trim = "Y" Then
                    field += ",ClassChg"
                    field1 += ",?ClassChg"
                End If



                If dr("EXtChg").ToString.Trim = "Y" Then
                    field += ",EXtChg"
                    field1 += ",?EXtChg"
                End If
                If dr("User1Chg").ToString.Trim = "Y" Then
                    field += ",User1Chg"
                    field1 += ",?User1Chg"
                End If
                If dr("DeptChg").ToString.Trim = "Y" Then
                    field += ",DeptChg"
                    field1 += ",?DeptChg"
                End If
                If dr("LocationChg").ToString.Trim = "Y" Then
                    field += ",LocationChg"
                    field1 += ",?LocationChg"
                End If

                If dr("ComputerNameChg").ToString.Trim = "Y" Then
                    field += ",ComputerNameChg"
                    field1 += ",?ComputerNameChg"
                End If

                If dr("ModelChg").ToString.Trim = "Y" Then
                    field += ",ModelChg"
                    field1 += ",?ModelChg"
                End If
                If dr("CPUChg").ToString.Trim = "Y" Then
                    field += ",CPUChg"
                    field1 += ",?CPUChg"
                End If


                If dr("FDDChg").ToString.Trim = "Y" Then
                    field += ",FDDChg"
                    field1 += ",?FDDChg"
                End If
                '50

                If dr("CD_RChg").ToString.Trim = "Y" Then
                    field += ",CD_RChg"
                    field1 += ",?CD_RChg"
                End If

                If dr("CD_RWChg").ToString.Trim = "Y" Then
                    field += ",CD_RWChg"
                    field1 += ",?CD_RWChg"
                End If
                If dr("MAC1Chg").ToString.Trim = "Y" Then
                    field += ",MAC1Chg"
                    field1 += ",?MAC1Chg"
                End If
                If dr("MAC2Chg").ToString.Trim = "Y" Then
                    field += ",MAC2Chg"
                    field1 += ",?MAC2Chg"
                End If
                If dr("PSChg").ToString.Trim = "Y" Then
                    field += ",PSChg"
                    field1 += ",?PSChg"
                End If

                If dr("NBLabelChg").ToString.Trim = "Y" Then
                    field += ",NBLabelChg"
                    field1 += ",?NBLabelChg"
                End If

                If dr("RemarkChg").ToString.Trim = "Y" Then
                    field += ",RemarkChg"
                    field1 += ",?RemarkChg"
                End If
                If dr("OSChg").ToString.Trim = "Y" Then
                    field += ",OSChg"
                    field1 += ",?OSChg"
                End If
                If dr("HDDChg").ToString.Trim = "Y" Then
                    field += ",HDDChg"
                    field1 += ",?HDDChg"
                End If
                '60
                If dr("RAMChg").ToString.Trim = "Y" Then
                    field += ",RAMChg"
                    field1 += ",?RAMChg"
                End If
                If dr("UsingTypeChg").ToString.Trim = "Y" Then
                    field += ",UsingTypeChg"
                    field1 += ",?UsingTypeChg"
                End If

                If dr("MonitorSizeChg").ToString.Trim = "Y" Then
                    field += ",MonitorSizeChg"
                    field1 += ",?MonitorSizeChg"
                End If
                If dr("FactoryChg").ToString.Trim = "Y" Then
                    field += ",FactoryChg"
                    field1 += ",?FactoryChg"
                End If

                If dr("storehouseChg").ToString.Trim = "Y" Then
                    field += ",storehouseChg"
                    field1 += ",?storehouseChg"
                End If

                If dr("StoreLocationChg").ToString.Trim = "Y" Then
                    field += ",StoreLocationChg"
                    field1 += ",?StoreLocationChg"
                End If

                If dr("UsingType2Chg").ToString.Trim = "Y" Then
                    field += ",UsingType2Chg"
                    field1 += ",?UsingType2Chg"
                End If

                If dr("HouseIDChg").ToString.Trim = "Y" Then
                    field += ",HouseIDChg"
                    field1 += ",?HouseIDChg"
                End If
                If dr("OtherRemarkChg").ToString.Trim = "Y" Then
                    field += ",OtherRemarkChg"
                    field1 += ",?OtherRemarkChg"
                End If


                If dr("UsingDescChg").ToString.Trim = "Y" Then
                    field += ",UsingDescChg"
                    field1 += ",?UsingDescChg"
                End If
                '69
                sb1.AppendLine(" insert into v_ast_assetalllog (" + field + ") values (" + field1 + ");")
                If dr("asset_status").ToString = "Scrapped" Then
                    sb1.AppendLine("Update v_ast_assetalllog  set isScrapped='1' where asset_serialNo=?asset_serialNo and ModiType='4'  and ModiDate<=?ModiDate;")
                End If

                cmd = New MySqlCommand(sb1.ToString(), con)
                cmd.Parameters.AddWithValue("?asset_serialNo", dr("asset_serialNo").ToString)

                If ModiType = "1" Then
                    If dr("asset_ownerChg").ToString.Trim = "Y" Then
                        If dr("asset_status").ToString = "Scrapped" Or dr("asset_status").ToString = "Scrapping" Then
                            cmd.Parameters.AddWithValue("?ModiType", "7")
                        Else
                            If dr("asset_status").ToString = "Storing" Then
                                cmd.Parameters.AddWithValue("?ModiType", "4")
                            Else
                                cmd.Parameters.AddWithValue("?ModiType", "2")
                            End If

                        End If

                    Else
                        If dr("asset_status").ToString = "Scrapped" Or dr("asset_status").ToString = "Scrapping" Then
                            cmd.Parameters.AddWithValue("?ModiType", "7")
                        Else
                            If dr("asset_status").ToString = "Storing" Then
                                cmd.Parameters.AddWithValue("?ModiType", "4")
                            Else
                                cmd.Parameters.AddWithValue("?ModiType", "1")
                            End If
                        End If
                    End If
                Else
                    If dr("asset_ownerChg").ToString.Trim = "Y" Then
                        If dr("asset_status").ToString = "Storing" Then
                            cmd.Parameters.AddWithValue("?ModiType", "4")
                        Else
                            cmd.Parameters.AddWithValue("?ModiType", "2")
                        End If
                    Else
                        If dr("asset_status").ToString = "Storing" Then
                            cmd.Parameters.AddWithValue("?ModiType", "4")
                        Else
                            cmd.Parameters.AddWithValue("?ModiType", ModiType)
                        End If
                    End If
                End If


                cmd.Parameters.AddWithValue("?SeqNo", max)
                cmd.Parameters.AddWithValue("?ModiUID", dr("ModiUID").ToString)
                cmd.Parameters.AddWithValue("?ModiDate", dr("CrtDate"))


                cmd.Parameters.AddWithValue("?assetdef_id", dr("assetdef_id"))

                cmd.Parameters.AddWithValue("?asset_owner", dr("asset_owner").ToString)

                cmd.Parameters.AddWithValue("?asset_status", dr("asset_status").ToString)

                cmd.Parameters.AddWithValue("?Class", dr("Class").ToString)



                cmd.Parameters.AddWithValue("?EXt", dr("EXt").ToString)
                '10
                cmd.Parameters.AddWithValue("?User1", dr("User1").ToString)

                cmd.Parameters.AddWithValue("?Dept", dr("Dept").ToString)

                cmd.Parameters.AddWithValue("?Location", dr("Location").ToString)

                cmd.Parameters.AddWithValue("?ComputerName", dr("ComputerName").ToString)

                cmd.Parameters.AddWithValue("?Model", dr("Model").ToString)

                cmd.Parameters.AddWithValue("?CPU", dr("CPU").ToString)

                cmd.Parameters.AddWithValue("?FDD", dr("FDD").ToString)

                cmd.Parameters.AddWithValue("?CD_R", dr("CD_R").ToString)

                cmd.Parameters.AddWithValue("?CD_RW", dr("CD_RW").ToString)

                cmd.Parameters.AddWithValue("?MAC1", dr("MAC1").ToString)
                '20
                cmd.Parameters.AddWithValue("?MAC2", dr("MAC2").ToString)

                cmd.Parameters.AddWithValue("?PS", dr("PS").ToString)

                cmd.Parameters.AddWithValue("?NBLabel", dr("NBLabel").ToString)

                'cmd.Parameters.AddWithValue("?Mail", dr("Mail").ToString)

                cmd.Parameters.AddWithValue("?Remark", dr("Remark").ToString)

                cmd.Parameters.AddWithValue("?OS", dr("OS").ToString)

                cmd.Parameters.AddWithValue("?HDD", dr("HDD").ToString)

                cmd.Parameters.AddWithValue("?RAM", dr("RAM").ToString)

                cmd.Parameters.AddWithValue("?UsingType", dr("UsingType").ToString)

                cmd.Parameters.AddWithValue("?MonitorSize", dr("MonitorSize").ToString)

                cmd.Parameters.AddWithValue("?Factory", dr("Factory").ToString)
                ''30
                cmd.Parameters.AddWithValue("?storehouse", dr("storehouse").ToString)

                cmd.Parameters.AddWithValue("?StoreLocation", dr("StoreLocation").ToString)

                cmd.Parameters.AddWithValue("?UsingType2", dr("UsingType2").ToString)

                cmd.Parameters.AddWithValue("?HouseID", dr("HouseID").ToString)

                cmd.Parameters.AddWithValue("?OtherRemark", dr("OtherRemark").ToString)



                cmd.Parameters.AddWithValue("?UsingDesc", dr("UsingDesc").ToString)


                '' ''=================================================5/10
                If dr("assetdef_idChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?assetdef_idChg", "Y")
                End If
                If dr("asset_ownerChg").ToString.Trim = "Y" Then
                    cmd.Parameters.AddWithValue("?asset_ownerChg", "Y")

                End If
                '40 
                'Dim fromDept As String
                'Dim uDao As OAMS.DAO.UsersDAO = New OAMS.DAO.UsersDAO(_dbName)
                'Try
                '    fromDept = uDao.GetEmpData(dr("asset_owner").ToString).Rows(0)("Dept_ID").ToString
                'Catch ex As Exception
                '    fromDept = ""
                'End Try
                If dr("asset_status").ToString = "Scrapped" Then
                    Dim dt As DataTable = logDao.GetFromDeptFromAssetOwner(dr("asset_serialNo").ToString)
                    If dr("asset_status") = "Storing" Then
                        If Not dt Is Nothing Then
                            cmd.Parameters.AddWithValue("?FromDept", dt.Rows(0)("FromDept").ToString.Trim)
                            cmd.Parameters.AddWithValue("?FromAsset_Owner", dt.Rows(0)("Fromasset_owner").ToString.Trim)
                        Else
                            cmd.Parameters.AddWithValue("?FromDept", dr("FromDept").ToString)
                            cmd.Parameters.AddWithValue("?FromAsset_Owner", dr("Fromasset_owner").ToString)
                        End If
                    Else
                        cmd.Parameters.AddWithValue("?FromDept", dr("FromDept").ToString)
                        cmd.Parameters.AddWithValue("?FromAsset_Owner", dr("Fromasset_owner").ToString)
                    End If
                Else
                    cmd.Parameters.AddWithValue("?FromDept", dr("FromDept").ToString)
                    cmd.Parameters.AddWithValue("?FromAsset_Owner", dr("Fromasset_owner").ToString)
                End If

               


                If dr("asset_statusChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?asset_statusChg", "Y")
                End If


                If dr("ClassChg").ToString.Trim = "Y" Then
                    cmd.Parameters.AddWithValue("?ClassChg", "Y")
                End If



                If dr("EXtChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?EXtChg", "Y")
                End If
                If dr("User1Chg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?User1Chg", "Y")
                End If
                If dr("DeptChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?DeptChg", "Y")
                End If
                If dr("LocationChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?LocationChg", "Y")
                End If

                If dr("ComputerNameChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?ComputerNameChg", "Y")
                End If

                If dr("ModelChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?ModelChg", "Y")
                End If
                If dr("CPUChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?CPUChg", "Y")
                End If


                If dr("FDDChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?FDDChg", "Y")
                End If

                '50
                If dr("CD_RChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?CD_RChg", "Y")
                End If

                If dr("CD_RWChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?CD_RWChg", "Y")
                End If
                If dr("MAC1Chg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?MAC1Chg", "Y")
                End If
                If dr("MAC2Chg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?MAC2Chg", "Y")
                End If
                If dr("PSChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?PSChg", "Y")
                End If

                If dr("NBLabelChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?NBLabelChg", "Y")
                End If

                If dr("RemarkChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?RemarkChg", "Y")
                End If
                If dr("OSChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?OSChg", "Y")
                End If
                If dr("HDDChg").ToString.Trim = "Y" Then

                    field1 += ",?HDDChg"
                    cmd.Parameters.AddWithValue("?HDDChg", "Y")
                End If
                If dr("RAMChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?RAMChg", "Y")
                End If
                If dr("UsingTypeChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?UsingTypeChg", "Y")
                End If
                '60
                If dr("MonitorSizeChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?MonitorSizeChg", "Y")
                End If
                If dr("FactoryChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?FactoryChg", "Y")
                End If

                If dr("storehouseChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?storehouseChg", "Y")
                End If

                If dr("StoreLocationChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?StoreLocationChg", "Y")
                End If

                If dr("UsingType2Chg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?UsingType2Chg", "Y")
                End If

                If dr("HouseIDChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?HouseIDChg", "Y")
                End If
                If dr("OtherRemarkChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?OtherRemarkChg", "Y")
                End If

                If dr("UsingDescChg").ToString.Trim = "Y" Then

                    cmd.Parameters.AddWithValue("?UsingDescChg", "Y")
                End If
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                con.Close()
                Throw (ex)
            Finally
            End Try
            Dim crtdate, senddate As DateTime
            If dr("asset_status").ToString.Trim <> "Distributed" Then ''不在使用中的資料,取消要回報的資料
                '檢查CrtDate>SendDate才可清除紀錄
                Try
                    crtdate = DateTime.Parse(dr("CrtDate").ToString)
                Catch ex As Exception
                    Exit Sub

                End Try
                Try
                    senddate = DateTime.Parse(dr("SendDate").ToString)
                Catch ex As Exception
                    Exit Sub

                End Try
                If crtdate > senddate Then
                    sb.Length = 0
                    sb.AppendLine("update V_Ast_AssetAll  set  ReturnDate=null,AppID=isnull(AppID,0)+1 ,ReturnBy='',ReturnDeadLine=null,ResendDays=0 ,ResendTimes=0 ,ResendTimesNow=0,ResendDate=null,MailSubject='',MailBody='' ,SendDate=null,ParamID= null where asset_serialNo='" + dr("asset_serialNo").ToString + "'")
                    SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString())

                End If

            End If



        End Sub

        ''' <summary>
        ''' 取得目前每個人的總數,欄位會橫排
        ''' </summary>
        ''' <param name="deptno"></param>
        ''' <param name="asset_owner"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>

        Public Function getUserAssetSum(ByVal deptno As String, ByVal asset_owner As String, ByVal asset_defid As String) As DataTable
            Dim sql As StringBuilder = New StringBuilder()

            sql.AppendLine(" select * from C_AST_ASSETDEF")

            sql.AppendLine(" Select  B.deptno ,A.asset_owner ,B.emp_name ,count(asset_serialNo) as Ttl")
            sql.AppendLine(" from       V_AST_ASSETALL A")
            sql.AppendLine(" left join AUOREF.dbo.emp_data_all B on A.asset_owner =B.emp_no   ")
            sql.AppendLine(" where(1 = 1) ")
            If deptno <> "" Then
                sql.AppendLine(" and b.deptno like @deptno+'%'")
            End If
            If asset_owner <> "" Then
                sql.AppendLine(" and A.asset_owner like @asset_owner+'%'")
            End If
            If asset_defid <> "" Then
                sql.AppendLine("and A.assetdef_id=" & asset_defid)
            End If

            sql.AppendLine(" group by B.deptno  ,A.asset_owner ,B.emp_name ")
            sql.AppendLine(" order by b.deptno,A.asset_owner ")

            sql.AppendLine(" Select  A.assetdef_id ,C.assetdef_nameEN ,B.deptno  ,A.asset_owner ,B.emp_name ,count(asset_serialNo) as defTtl ")
            sql.AppendLine(" from       V_AST_ASSETALL A")
            sql.AppendLine(" left join AUOREF.dbo.emp_data_all B on A.asset_owner =B.emp_no ")
            sql.AppendLine(" left join C_AST_ASSETDEF C on A.assetdef_id =C.def_id ")
            sql.AppendLine(" where(1 = 1)")
            If deptno <> "" Then
                sql.AppendLine(" and b.deptno like @deptno+'%'")
            End If
            If asset_owner <> "" Then
                sql.AppendLine(" and A.asset_owner like @asset_owner+'%'")
            End If
            If asset_defid <> "" Then
                sql.AppendLine("and A.assetdef_id=" & asset_defid)
            End If
            sql.AppendLine(" group by A.assetdef_id,C.assetdef_nameEN  ,B.deptno  ,A.asset_owner ,B.emp_name ")
            sql.AppendLine(" order by b.deptno,A.asset_owner ")
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@deptno", SqlDbType.VarChar)
            sqlParams(0).Value = deptno

            sqlParams(1) = New SqlParameter("@asset_owner", SqlDbType.VarChar)
            sqlParams(1).Value = asset_owner
            

            Dim ds As DataSet = SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sql.ToString(), sqlParams)
            Dim dt As DataTable = ds.Tables(1)
            For Each dr As DataRow In ds.Tables(0).Rows
                dt.Columns.Add(dr("assetdef_nameEN").ToString().Replace(" ", ""), Type.GetType("System.Double"))
            Next

            Dim drs() As DataRow
            For Each dr1 As DataRow In ds.Tables(2).Rows
                drs = dt.Select("asset_owner='" & dr1("asset_owner") & "'")
                If drs.Length > 0 Then
                    Try
                        drs(0)(dr1("assetdef_nameEN").ToString().Replace(" ", "")) = dr1("defTtl")
                    Catch ex As Exception

                    End Try

                End If

            Next


            Return dt

        End Function
       

    End Class

End Namespace
